--=================================
--SQLspTest.sql
--测试dbEmployees..usp_HiredAfter
--读入测试用例数据并把测试结果写回

set nocount on
if not exists(select * from master.dbo.sysdatabases where name='DbTestCasesAndResults')
 raiserror('Fatal error:dbTestCasesAndResults not found',16,1)
go

if exists(select * from sysobjects where name='tap_Reset')
 drop procedure tap_Reset
go

create procedure tap_Reset
as
 truncate table dbEmployees.dbo.tb1Employees

insert into dbEmployees.dbo.tb1Employees values('e11','Adams','10/25/2009')
insert into dbEmployees.dbo.tb1Employees values('e22','Baker','10/12/2009')
insert into dbEmployees.dbo.tb1Employees values('e33','Young','06/15/2001')
insert into dbEmployees.dbo.tb1Employees values('e44','Zetta','08/15/2003')

--其他数据在此处插入
exec tap_Reset
go

declare tCursor cursor fast_forward
 for select caseID,input,expectedChecksum
  from DbTestCasesAndResults.dbo.tb1Testcases
  order by caseID

declare @caseID char(4),@input datetime,@expectedChecksum int
declare @whenRun datetime
declare @resultMsg varchar(80)
declare @actualChecksum int

create table #resultRowset --for checksum technique
(
 empID char(3) primary key,
 empLast varchar(35) not null,
 empDOH datetime not null
)

set @whenRun = getdate()

print 'stored procedure under test = usp_HiredAfter'
print ' '
print 'CaseID Input Expected Actual Result'
print '==========================================='

open tCursor
fetch next
 from tCursor
 into @caseID,@input,@expectedChecksum

while @@fetch_status=0
begin
 exec tap_Reset --reset test bed data
 truncate table #resultRowset --empty out the result rowset
 insert #resultRowset(empID,empLast,empDOH) --call sp under test
 exec dbEmployees.dbo.usp_HiredAfter @input
 
 if(@@rowcount=0)
  set @actualChecksum=0
 else
  select @actualChecksum=checksum_agg(binary_checksum(*)) from #resultRowset
 
 if (@actualChecksum=@expectedChecksum)
  begin
   set @resultMsg=@caseID + ' ' + cast(@input as varchar(11)) +
   ' ' + cast(@expectedChecksum as varchar(20)) + ' ' +
      cast(@actualChecksum as varchar(20)) + 'Pass'
   print @resultMsg
   insert into dbTestCasesAndResults.dbo.tb1Results values(@caseID,'Pass',@whenRun)
  end
 else
  begin
   set @resultMsg=@caseID + ' ' + cast(@input as varchar(11)) +
   ' ' + cast(@expectedChecksum as varchar(20)) + ' ' +
      cast(@actualChecksum as varchar(20)) + 'FAIL'
   print @resultMsg
   insert into dbTestCasesAndResults.dbo.tb1Results values(@caseID,'FAIL',@whenRun)
  end
 fetch next
  from tCursor
  into @caseID,@input,@expectedChecksum

end

close tCursor
deallocate tCursor

drop table #resultRowset