此部分内容创建一个轻量级T-SQL测试套件,总共有3个脚本:
用于创建测试平台数据和待测存储过程的脚本
--====================== --makeDbTestAndResults.sql
use master go
if exists (select * from sysdatabases where name = 'DbTestAndResults') drop database makeDbTestAndResults go
if exists (select * from sysxlogins where name = 'testLogin') exec sp_droplogin 'testLogin' go
create database makeDbTestAndResults go
use makeDbTestAndResults go
create table tb1TestCases ( caseID char(4) primary key, input datetime not null, expectedChecksum int not null ) go
--下面插入的是用于usp_HiredAfter的测试数据,采用了对期望值求校验和的方法 --也可以通过BCP,BTS或者C#程序从文本文件读入这些数据
insert into tb1TestCases values('0001','10/25/2009',1042032) insert into tb1TestCases values('0002','10/25/2009',9999999)--deliberate error insert into tb1TestCases values('0003','10/25/2008',25527856) insert into tb1TestCases values('0004','10/10/2006',1042032) go
create table tb1Results ( caseID char(4) not null, result char(4) not null, whenRun datetime not null ) go
exec sp_addlogin 'testLogin','secret' go
exec sp_grantdbaccess 'testLogin' go
grant select,insert,delete,update on tb1TestCases to testLogin go
grant select,insert,delete,update on tb1Results to testLogin go
--结束脚本
用于创建测试例例数据和测试结果存储的脚本
--========================== --makeDbEmployees.sql use master go
if exists(select * from sysdatabases where name = 'dbEmployees') drop database dbEmployees go
if exists(select * from sysxlogins where name = 'employeesLogin') exec sp_droplogin 'employeesLogin' go
create database dbEmployees go
use dbEmployees go
create table tb1Employees ( empID char(3) primary key, empLast varchar(35) not null, empDOH datetime not null, ) go
insert into tb1Employees values('e11','Adams','10/25/2009') insert into tb1Employees values('e22','Baker','10/25/2009') go
exec sp_addlogin 'employeesLogin','secret' go
exec sp_grantdbaccess 'employeesLogin' go
create procedure usp_HiredAfter @dt datetime as select * from tb1Employees where empDOH > @dt go
grant execute on usp_HiredAfter to employeesLogin go
--end script
自动化测试套件脚本
--================================= --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
|