When enabled, provides results for evaluation. One can use this information to mark the result pass or fail.
Stored procedure name to evaluate result is “Stored Procedure Prefix”_EvaluateResults_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_EvaluateResults_Proc:
use test
go
drop proc SQLTest1_EvaluateResults_Proc
go
drop table SQLTest1_EvaluateResults_Log_Table
go
create table SQLTest1_EvaluateResults_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, Row nvarchar(max), Response nvarchar(max))
go
drop table SQLTest1_EvaluateResults_Rows_4509F328A9ED45D496F6B21FA48D3809_Table
go
create table SQLTest1_EvaluateResults_Rows_4509F328A9ED45D496F6B21FA48D3809_Table (RowNumber int, Row nvarchar(max))
go
drop table SQLTest1_EvaluateResults_Rows_001BD879CDB44F4581C924F630870776_Table
go
create table SQLTest1_EvaluateResults_Rows_001BD879CDB44F4581C924F630870776_Table (RowNumber int, Row nvarchar(max))
go
create proc SQLTest1_EvaluateResults_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, @Row nvarchar(max))
as
begin
declare @tab table (id int)
declare @ResponseOutput nvarchar(max) = ''
insert into SQLTest1_EvaluateResults_Log_Table (ctime, SQLTestInstanceGUID, SQLTestWorkloadGUID, SQLTestWorkloadName, Workload, Connection, ConnectionString, Thread, BatchIndex, CommandText, Iteration, ResultSetIndex, RowNumber, Row) output inserted.id into @tab select getdate(), @SQLTestInstanceGUID, @SQLTestWorkloadGUID, @SQLTestWorkloadName, @Workload, @Connection, @ConnectionString, @Thread, @BatchIndex, @CommandText, @Iteration, @ResultSetIndex, @RowNumber, @Row
if(@CommandText like '%4509F328A9ED45D496F6B21FA48D3809%')
begin
if (@RowNumber = 0)
begin
delete SQLTest1_EvaluateResults_Rows_4509F328A9ED45D496F6B21FA48D3809_Table
insert into SQLTest1_EvaluateResults_Rows_4509F328A9ED45D496F6B21FA48D3809_Table values (0, 'c1, c2, c3.')
insert into SQLTest1_EvaluateResults_Rows_4509F328A9ED45D496F6B21FA48D3809_Table values (1, '1, 1, ' + replicate('a', 2000) + '.')
insert into SQLTest1_EvaluateResults_Rows_4509F328A9ED45D496F6B21FA48D3809_Table values (2, '2, 2, ' + replicate('a', 2000) + '.')
if exists (select RowNumber from SQLTest1_EvaluateResults_Rows_4509F328A9ED45D496F6B21FA48D3809_Table where Row = @Row)
begin
delete SQLTest1_EvaluateResults_Rows_4509F328A9ED45D496F6B21FA48D3809_Table where RowNumber = 0
set @ResponseOutput = '1 -- pass.'
end
else
set @ResponseOutput = '2 -- fail, Unknown row header.'
end
else if (@row is not NULL)
begin
if exists (select RowNumber from SQLTest1_EvaluateResults_Rows_4509F328A9ED45D496F6B21FA48D3809_Table where Row = @Row)
begin
delete top (1) SQLTest1_EvaluateResults_Rows_4509F328A9ED45D496F6B21FA48D3809_Table where Row = @Row
set @ResponseOutput = '1 -- pass.'
end
else
set @ResponseOutput = '2 -- fail, Unknown row.'
end
else
begin
if not exists (select RowNumber from SQLTest1_EvaluateResults_Rows_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_EvaluateResults_Rows_001BD879CDB44F4581C924F630870776_Table
insert into SQLTest1_EvaluateResults_Rows_001BD879CDB44F4581C924F630870776_Table values (0, 'c1, c2, c3.')
insert into SQLTest1_EvaluateResults_Rows_001BD879CDB44F4581C924F630870776_Table values (1, '1, 1, ' + replicate('a', 2000) + '.')
insert into SQLTest1_EvaluateResults_Rows_001BD879CDB44F4581C924F630870776_Table values (2, '2, 2, ' + replicate('a', 2000) + '.')
if exists (select RowNumber from SQLTest1_EvaluateResults_Rows_001BD879CDB44F4581C924F630870776_Table where Row = @Row)
begin
delete SQLTest1_EvaluateResults_Rows_001BD879CDB44F4581C924F630870776_Table where RowNumber = 0
set @ResponseOutput = '1 -- pass.'
end
else
set @ResponseOutput = '2 -- fail, Unknown row header.'
end
else if (@row is not NULL)
begin
if exists (select RowNumber from SQLTest1_EvaluateResults_Rows_001BD879CDB44F4581C924F630870776_Table where RowNumber = @RowNumber and Row = @Row)
begin
delete top (1) SQLTest1_EvaluateResults_Rows_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_EvaluateResults_Rows_001BD879CDB44F4581C924F630870776_Table)
set @ResponseOutput = '1 -- pass.'
else
set @ResponseOutput = '2 -- fail, Unknown row.'
end
end
if (@ResponseOutput = '')
set @ResponseOutput = '0 -- no evaluation'
update SQLTest1_EvaluateResults_Log_Table set Response = @ResponseOutput where id = (select id from @tab)
select @ResponseOutput
end
go
select * from SQLTest1_EvaluateResults_Log_Table
go
Please help us improve this page by entering your comments and suggestions below: