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: