Evaluate Results

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:


  • Captcha image