自动化测试套件脚本：
--=================================
--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
--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
