Evaluate Checksum
When enabled provides checksum for evaluation. One can use this information to mark the result pass or fail.
Stored procedure name to evaluate checksum is “Stored Procedure Prefix”_EvaluateChecksum_Proc.
The below example provides SQL command and the results evaluation script.
The below example uses “Data Source=SQLWorkshops\SQL2012;Database=test;Integrated Security=true” as “Connection String” and “SQLTest1” as “Stored Procedure Prefix” in test database.
When the unit test stored procedure is called, it has to return a string. If the string starts with “0” to “6”, it is handled as described below, anything else is considered an exception.
“0” – considered as no event, neither pass nor fail.
“1” – considered test pass.
“2” – considered test fail.
“3” – considered test pass skip rest of the test.
“4” – considered test pass skip rest of the SQL commands.
“5” – considered test fail skip rest of the test.
“6” – considered test fail skip rest of the SQL commands.
“?” – considered as an exception.
SQL command:
use test
if (object_id('tab73') is not NULL)
drop table tab73
go
use test
create table tab73 (c1 int primary key clustered, c2 int, c3 char(2000))
go
use test
insert into tab73 values (1, 1, replicate('a', 2000))
go
use test
insert into tab73 values (2, 2, replicate('a', 2000))
go
use test
insert into tab73 values (3, 3, replicate('a', 2000))
go
use test
select c1, c2, c3 from tab73 where c1 <= 2 --4509F328A9ED45D496F6B21FA48D3809
go
use test
select c1, c2, c3 from tab73 where c1 <= 2 union all select c1, c2, c3 from tab73 where c1 <= 2 --4509F328A9ED45D496F6B21FA48D3809
go
use test
select c1, c2, c3 from tab73 where c1 <= 2 order by c1 --001BD879CDB44F4581C924F630870776
go
use test
select c1, c2, c3 from tab73 where c1 <= 2 order by c1 desc --001BD879CDB44F4581C924F630870776
go
SQLTest1_EvaluateChecksum_Proc:
use test
go
drop proc SQLTest1_EvaluateChecksum_Proc
go
drop table SQLTest1_EvaluateChecksum_Log_Table
go
create table SQLTest1_EvaluateChecksum_Log_Table (id int identity primary key, ctime datetime, SQLTestInstanceGUID uniqueidentifier, SQLTestWorkloadGUID uniqueidentifier, SQLTestWorkloadName nvarchar(256), Workload int, Connection int, ConnectionString nvarchar(max), Thread int, BatchIndex int, CommandText nvarchar(max), Iteration int, ResultSetIndex int, RowNumber int, Checksum nvarchar(max), Response nvarchar(max))
go
drop table SQLTest1_EvaluateChecksum_Checksums_4509F328A9ED45D496F6B21FA48D3809_Table
go
create table SQLTest1_EvaluateChecksum_Checksums_4509F328A9ED45D496F6B21FA48D3809_Table (RowNumber int, Checksum nvarchar(max))
go
drop table SQLTest1_EvaluateChecksum_Checksums_001BD879CDB44F4581C924F630870776_Table
go
create table SQLTest1_EvaluateChecksum_Checksums_001BD879CDB44F4581C924F630870776_Table (RowNumber int, Checksum nvarchar(max))
go
create proc SQLTest1_EvaluateChecksum_Proc (@SQLTestInstanceGUID uniqueidentifier, @SQLTestWorkloadGUID uniqueidentifier, @SQLTestWorkloadName nvarchar(256), @Workload int, @Connection int, @ConnectionString nvarchar(max), @Thread int, @BatchIndex int, @CommandText nvarchar(max), @Iteration int, @ResultSetIndex int, @RowNumber int, @Checksum nvarchar(max))
as
begin
declare @tab table (id int)
declare @ResponseOutput nvarchar(max) = ''
insert into SQLTest1_EvaluateChecksum_Log_Table (ctime, SQLTestInstanceGUID, SQLTestWorkloadGUID, SQLTestWorkloadName, Workload, Connection, ConnectionString, Thread, BatchIndex, CommandText, Iteration, ResultSetIndex, RowNumber, Checksum) output inserted.id into @tab select getdate(), @SQLTestInstanceGUID, @SQLTestWorkloadGUID, @SQLTestWorkloadName, @Workload, @Connection, @ConnectionString, @Thread, @BatchIndex, @CommandText, @Iteration, @ResultSetIndex, @RowNumber, @Checksum
if(@CommandText like '%4509F328A9ED45D496F6B21FA48D3809%')
begin
if (@RowNumber = 0)
begin
delete SQLTest1_EvaluateChecksum_Checksums_4509F328A9ED45D496F6B21FA48D3809_Table
insert into SQLTest1_EvaluateChecksum_Checksums_4509F328A9ED45D496F6B21FA48D3809_Table values (0, 'b3OoH8xuk1IuH+b+ws3L0mgI4Fp5upnukMbAU3fwKQY=')
insert into SQLTest1_EvaluateChecksum_Checksums_4509F328A9ED45D496F6B21FA48D3809_Table values (1, 'HOQ2830QfqywYg93XPKAYEAzZj4X4/20seEHEb9AR6s=')
insert into SQLTest1_EvaluateChecksum_Checksums_4509F328A9ED45D496F6B21FA48D3809_Table values (2, 'I4OA4sMygJ8mEvfGVVVPjI3OFT1wZrKIs92Xh45ZiyM=')
if exists (select RowNumber from SQLTest1_EvaluateChecksum_Checksums_4509F328A9ED45D496F6B21FA48D3809_Table where Checksum = @Checksum)
begin
delete SQLTest1_EvaluateChecksum_Checksums_4509F328A9ED45D496F6B21FA48D3809_Table where RowNumber = 0
set @ResponseOutput = '1 -- pass.'
end
else
set @ResponseOutput = '2 -- fail, Unknown row header.'
end
else if (@Checksum is not NULL)
begin
if exists (select RowNumber from SQLTest1_EvaluateChecksum_Checksums_4509F328A9ED45D496F6B21FA48D3809_Table where Checksum = @Checksum)
begin
delete top (1) SQLTest1_EvaluateChecksum_Checksums_4509F328A9ED45D496F6B21FA48D3809_Table where Checksum = @Checksum
set @ResponseOutput = '1 -- pass.'
end
else
set @ResponseOutput = '2 -- fail, Unknown row.'
end
else
begin
if not exists (select RowNumber from SQLTest1_EvaluateChecksum_Checksums_4509F328A9ED45D496F6B21FA48D3809_Table)
set @ResponseOutput = '1 -- pass.'
else
set @ResponseOutput = '2 -- fail, Unknown row.'
end
end
if(@CommandText like '%001BD879CDB44F4581C924F630870776%')
begin
if (@RowNumber = 0)
begin
delete SQLTest1_EvaluateChecksum_Checksums_001BD879CDB44F4581C924F630870776_Table
insert into SQLTest1_EvaluateChecksum_Checksums_001BD879CDB44F4581C924F630870776_Table values (0, 'b3OoH8xuk1IuH+b+ws3L0mgI4Fp5upnukMbAU3fwKQY=')
insert into SQLTest1_EvaluateChecksum_Checksums_001BD879CDB44F4581C924F630870776_Table values (1, 'HOQ2830QfqywYg93XPKAYEAzZj4X4/20seEHEb9AR6s=')
insert into SQLTest1_EvaluateChecksum_Checksums_001BD879CDB44F4581C924F630870776_Table values (2, 'I4OA4sMygJ8mEvfGVVVPjI3OFT1wZrKIs92Xh45ZiyM=')
if exists (select RowNumber from SQLTest1_EvaluateChecksum_Checksums_001BD879CDB44F4581C924F630870776_Table where Checksum = @Checksum)
begin
delete SQLTest1_EvaluateChecksum_Checksums_001BD879CDB44F4581C924F630870776_Table where RowNumber = 0
set @ResponseOutput = '1 -- pass.'
end
else
set @ResponseOutput = '2 -- fail, Unknown row header.'
end
else if (@Checksum is not NULL)
begin
if exists (select RowNumber from SQLTest1_EvaluateChecksum_Checksums_001BD879CDB44F4581C924F630870776_Table where RowNumber = @RowNumber and Checksum = @Checksum)
begin
delete top (1) SQLTest1_EvaluateChecksum_Checksums_001BD879CDB44F4581C924F630870776_Table where RowNumber = @RowNumber
set @ResponseOutput = '1 -- pass.'
end
else
set @ResponseOutput = '2 -- fail, Unknown row.'
end
else
begin
if not exists (select RowNumber from SQLTest1_EvaluateChecksum_Checksums_001BD879CDB44F4581C924F630870776_Table)
set @ResponseOutput = '1 -- pass.'
else
set @ResponseOutput = '2 -- fail, Unknown row.'
end
end
if (@ResponseOutput = '')
set @ResponseOutput = '0 -- no evaluation'
update SQLTest1_EvaluateChecksum_Log_Table set Response = @ResponseOutput where id = (select id from @tab)
select @ResponseOutput
end
go
select * from SQLTest1_EvaluateChecksum_Log_Table
go
Please help us improve this page by entering your comments and suggestions below: