Evaluate Records Affected

When enabled, provides records affected count for evaluation. One can use this information to mark the result pass or fail.

Stored procedure name to evaluate result is “Stored Procedure Prefix”_EvaluateRecordsAffected_Proc.

The below example provides SQL command and the records affected 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)) --51400277160C47D7BCBE57453F8BA098

go

use test

insert into tab73 values (2, 2, replicate('a', 2000))

insert into tab73 values (3, 3, replicate('a', 2000))

insert into tab73 values (4, 4, replicate('a', 2000))

insert into tab73 values (5, 5, replicate('a', 2000)) --8D19FAB501FC4D869555F0962073DE61

go

use test

insert into tab73 values (6, 6, replicate('a', 2000))

insert into tab73 values (7, 7, replicate('a', 2000)) --8D19FAB501FC4D869555F0962073DE61

go

use test

select c1, c2, c3 from tab73 --30821B9172234F678299D5867C155675

go

use test

update tab73 set c2 = 10 where c1 <= 2 --4E9C19539915404798EEA9F7C6CD34B1

go

use test

update tab73 set c2 = 10 where c1 <= 3 --4E9C19539915404798EEA9F7C6CD34B1

go

use test

delete tab73 where c1 <= 4 --3083492FA7AA43D3B01F8A5BF8F6AFC0

go

use test

delete tab73 where c1 <= 4 --3083492FA7AA43D3B01F8A5BF8F6AFC0

go

SQLTest1_EvaluateRecordsAffected_Proc:

use test

go

drop proc SQLTest1_EvaluateRecordsAffected_Proc

go

drop table SQLTest1_EvaluateRecordsAffected_Log_Table

go

create table SQLTest1_EvaluateRecordsAffected_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, RecordsAffected int, Response nvarchar(max))

go

create proc SQLTest1_EvaluateRecordsAffected_Proc (@SQLTestInstanceGUID uniqueidentifier, @SQLTestWorkloadGUID uniqueidentifier, @SQLTestWorkloadName nvarchar(256), @Workload int, @Connection int, @ConnectionString nvarchar(max), @Thread int, @BatchIndex int, @CommandText nvarchar(max), @Iteration int, @RecordsAffected int)

as

begin

    declare @tab table (id int)

    declare @ResponseOutput nvarchar(max) = ''

    insert into SQLTest1_EvaluateRecordsAffected_Log_Table (ctime, SQLTestInstanceGUID, SQLTestWorkloadGUID, SQLTestWorkloadName, Workload, Connection, ConnectionString, Thread, BatchIndex, CommandText, Iteration, RecordsAffected) output inserted.id into @tab select getdate(), @SQLTestInstanceGUID, @SQLTestWorkloadGUID, @SQLTestWorkloadName, @Workload, @Connection, @ConnectionString, @Thread, @BatchIndex, @CommandText, @Iteration, @RecordsAffected

    if(@CommandText like '%51400277160C47D7BCBE57453F8BA098%')

    begin

        if (@RecordsAffected = 1)

            set @ResponseOutput = '1 -- pass.'

        else

            set @ResponseOutput = '6 -- fail, skip SQL command, Incorrect records affected count.'

    end

    if(@CommandText like '%8D19FAB501FC4D869555F0962073DE61%')

    begin

        if (@RecordsAffected = 4)

            set @ResponseOutput = '1 -- pass.'

        else

            set @ResponseOutput = '6 -- fail, skip SQL command, Incorrect records affected count.'

    end

    if(@CommandText like '%30821B9172234F678299D5867C155675%')

    begin

        if (@RecordsAffected = -1)

            set @ResponseOutput = '1 -- pass.'

        else

            set @ResponseOutput = '6 -- fail, skip SQL command, Incorrect records affected count.'

    end

    if(@CommandText like '%4E9C19539915404798EEA9F7C6CD34B1%')

    begin

        if (@RecordsAffected = 2)

            set @ResponseOutput = '1 -- pass.'

        else

            set @ResponseOutput = '6 -- fail, skip SQL command, Incorrect records affected count.'

    end

    if(@CommandText like '%3083492FA7AA43D3B01F8A5BF8F6AFC0%')

    begin

        if (@RecordsAffected = 4)

            set @ResponseOutput = '1 -- pass.'

        else if (@RecordsAffected < 4)

            set @ResponseOutput = '4 -- pass, skip SQL command, Records already deleted.'

        else

            set @ResponseOutput = '6 -- fail, skip SQL command, Incorrect records affected count.'

    end

    if (@ResponseOutput = '')

        set @ResponseOutput = '0 -- no evaluation'

    update SQLTest1_EvaluateRecordsAffected_Log_Table set Response = @ResponseOutput where id = (select id from @tab)

    select @ResponseOutput

end

go

select * from SQLTest1_EvaluateRecordsAffected_Log_Table

go

Please help us improve this page by entering your comments and suggestions below:


  • Captcha image