Evaluate DB Calls
When enabled, provides error and execution time information for evaluation. One can use this information to mark the call pass or fail.
Stored procedure name to evaluate database calls is “Stored Procedure Prefix”_EvaluateDBCalls_Proc.
The below example provides SQL command and the database call 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
drop table tab73 --A08427BDB2AF4AB9AF1E3FA01F1503D7
go
use test
drop table tab73 --A08427BDB2AF4AB9AF1E3FA01F1503D7
go
use test
create table tab73 (c1 int primary key clustered, c2 int, c3 char(2000)) --72DE6142628D447EB6C6F07C6CDD37B4
go
use test
create table tab73 (c1 int primary key clustered, c2 int, c3 char(2000)) --72DE6142628D447EB6C6F07C6CDD37B4
go
use test
insert into tab73 values (1, 1, replicate('a', 2000)) --87B3379861DC47CC8CB0A226A4E4D750
go
use test
insert into tab73 values (1, 1, replicate('a', 2000)) --87B3379861DC47CC8CB0A226A4E4D750
go
use test
insert into tab73 values (2, 2, replicate('a', 2000)) --87B3379861DC47CC8CB0A226A4E4D750
go
use test
insert into tab73 values (3, 3, replicate('a', 2000)) --87B3379861DC47CC8CB0A226A4E4D750
go
SQLTest1_EvaluateDBCalls_Proc:
use test
go
drop proc SQLTest1_EvaluateDBCalls_Proc
go
drop table SQLTest1_EvaluateDBCalls_Log_Table
go
create table SQLTest1_EvaluateDBCalls_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, DBCall nvarchar(max), Number int, Class int, State int, LineNumber int, Message nvarchar(max), DBTime int, Response nvarchar(max))
go
create proc SQLTest1_EvaluateDBCalls_Proc (@SQLTestInstanceGUID uniqueidentifier, @SQLTestWorkloadGUID uniqueidentifier, @SQLTestWorkloadName nvarchar(256), @Workload int, @Connection int, @ConnectionString nvarchar(max), @Thread int, @BatchIndex int, @CommandText nvarchar(max), @Iteration int, @DBCall nvarchar(max), @Number int, @Class int, @State int, @LineNumber int, @Message nvarchar(max), @DBTime int)
as
begin
declare @tab table (id int)
declare @ResponseOutput nvarchar(max) = ''
insert into SQLTest1_EvaluateDBCalls_Log_Table (ctime, SQLTestInstanceGUID, SQLTestWorkloadGUID, SQLTestWorkloadName, Workload, Connection, ConnectionString, Thread, BatchIndex, CommandText, Iteration, DBCall, Number, Class, State, LineNumber, Message, DBTime) output inserted.id into @tab select getdate(), @SQLTestInstanceGUID, @SQLTestWorkloadGUID, @SQLTestWorkloadName, @Workload, @Connection, @ConnectionString, @Thread, @BatchIndex, @CommandText, @Iteration, @DBCall, @Number, @Class, @State, @LineNumber, @Message, @DBTime
if(@DBCall = 'Execute Reader' and @CommandText like '%A08427BDB2AF4AB9AF1E3FA01F1503D7%')
begin
if (@Number = 3701)
set @ResponseOutput = '4 -- pass, skip SQL Command, Table was already dropped'
if (@DBTime > 10000000)
set @ResponseOutput = '5 -- fail, skip Test, Too slow.'
end
if(@DBCall = 'Execute Reader' and @CommandText like '%72DE6142628D447EB6C6F07C6CDD37B4%')
begin
if (@Number = 2714)
set @ResponseOutput = '4 -- pass, skip SQL Command, Table was already created.'
if (@DBTime > 10000000)
set @ResponseOutput = '5 -- fail, skip Test, Too slow.'
end
if(@DBCall = 'Execute Reader' and @CommandText like '%87B3379861DC47CC8CB0A226A4E4D750%')
begin
if (@Number = 2627)
set @ResponseOutput = '6 -- fail, skip SQL Command, Duplicate Key.'
if (@DBTime > 10000000)
set @ResponseOutput = '5 -- fail, skip Test, Too slow.'
end
if (@ResponseOutput = '')
set @ResponseOutput = '0 -- no evaluation'
update SQLTest1_EvaluateDBCalls_Log_Table set Response = @ResponseOutput where id = (select id from @tab)
select @ResponseOutput
end
go
select * from SQLTest1_EvaluateDBCalls_Log_Table
go
Please help us improve this page by entering your comments and suggestions below: