22 July 2009 07:14
tonyrogerson
Testing Stored Procedures and Triggers - Using CHECKSUM to write effective test scripts for Stored Procedures and Triggers
Stored procedure and Trigger testing can be very easily compartmentalised into individual test scripts and fully automated by taking advantage of the CHECKSUM function – no more writing hoodles of IF statements to check each scenario – just write the query, if its changed the CHECKSUM on the results will be different.
Here goes:
Our Test object:-
create table Trades (
TradeID int not null identity primary key clustered,
TradeDate date not null,
TradeType tinyint not null,
TradeAmount decimal( 28, 2 ) not null,
RollingTradeAmountTotal decimal( 28, 2 ) not null default( 0 ),
)
go
create trigger trg_Trades_i on Trades for insert
as
begin
update tu
set RollingTradeAmountTotal = coalesce( t.RollingTradeAmountTotal, 0 ) + i.TradeAmount
from inserted i
inner join Trades tu on tu.TradeID = i.TradeID
left outer join Trades t on t.TradeID = ( select top 1 t2.TradeID
from Trades t2
where t2.TradeId < i.TradeID
order by t2.TradeID desc )
end
go
We want to test the above works and if changed the change does not alter the test outcome.
Here is how we test:
truncate table Trades
insert Trades ( TradeDate, TradeType, TradeAmount )
values( '20090721', 1, 10 )
insert Trades ( TradeDate, TradeType, TradeAmount )
values( '20090721', 1, 10 )
insert Trades ( TradeDate, TradeType, TradeAmount )
values( '20090721', 1, 10 )
insert Trades ( TradeDate, TradeType, TradeAmount )
values( '20090721', 1, 10 )
select *
from Trades
Now you manually check your results and can see that the RollingTradeAmountTotal does in fact increment accordingly.
We can now freeze frame the results using CHECKSUM...
declare @chksum int
set @chksum = CHECKSUM( cast( (
select TradeDate, TradeType, TradeAmount, RollingTradeAmountTotal
from Trades
order by TradeID
for xml path( '' )
) as varchar(max) ) )
print @chksum
if @chksum = 1111078251
print 'query results are fine'
else
raiserror( 'query results are different', 16, 1 )
If the results of the query change at all then the CHECKSUM will be different and thus the test script will fail.
This only works for non-changing columns, so you may need to take any IDENTITY or NEWID populated columns out of the equation; any dates – just fix them in time. If your logic is time based then you have a slightly more challenging task but it is workaround able.
Say, our trigger changes to this (uses an inner join instead of left outer thus missing the first row)...
create trigger trg_Trades_i on Trades for insert
as
begin
update tu
set RollingTradeAmountTotal = coalesce( t.RollingTradeAmountTotal, 0 ) + i.TradeAmount
from inserted i
inner join Trades tu on tu.TradeID = i.TradeID
inner join Trades t on t.TradeID = ( select top 1 t2.TradeID
from Trades t2
where t2.TradeId < i.TradeID
order by t2.TradeID desc )
end
go
Now run our test again and you will see the checksum is different so flags we need to investigate the problem:
truncate table Trades
insert Trades ( TradeDate, TradeType, TradeAmount )
values( '20090721', 1, 10 )
insert Trades ( TradeDate, TradeType, TradeAmount )
values( '20090721', 1, 10 )
insert Trades ( TradeDate, TradeType, TradeAmount )
values( '20090721', 1, 10 )
insert Trades ( TradeDate, TradeType, TradeAmount )
values( '20090721', 1, 10 )
select *
from Trades
-- The checksum...
declare @chksum int
set @chksum = CHECKSUM( cast( (
select TradeDate, TradeType, TradeAmount, RollingTradeAmountTotal
from Trades
order by TradeID
for xml path( '' )
) as varchar(max) ) )
print @chksum
if @chksum = 1111078251
print 'query results are fine'
else
raiserror( 'query results are different', 16, 1 )
Filed under: SQL Server, SQL Development