SQL Unit Testing with TSQLUNIT

Question

I noticed something weird while unit testing an update/insert stored procedure using TSQLUNIT on SQL Server 2012. when I call Exec tsu_RunTests, my test procedure runs but with unexpected behaviour. the line in the code that calls my original stored procedure is executed but no actual updates or inserts made to the database table as expected. Is there a valid reason for this behaviour? Or is this a bug I need to pay much attention to? I notice that when I execute the same original stored procedure outside of the test procedure, it works fine.

Answer 1

You can use Default parameter for each stored procedure and set this @IsTest parameter to true when use these stored procedures in tsu_RunTests.

CREATE PROCEDURE orginal_proc
    --@parameter_name 
    @IsTest BIT = 0
AS
    if @IsTest <> 1 Begin
    --  Test statements
    End Else Begin
    --  statements
    End 
GO

you also can use @@NESTLEVEL for check that your procedure execute directly or execute by other procedure.

CREATE PROCEDURE orginal_proc
    --@parameter_name 
AS

    if @@NESTLEVEL <> 1 Begin
    --  Test statements
    End Else Begin
    --  statements
    End
GO

EDIT : Stored Procedure Code must be like below :

If @@NESTLEVEL <> 1 Print 'Befor Update Message'
If @@NESTLEVEL = 1 Begin
   Update YourTable
   Set ...
End
If @@NESTLEVEL <> 1 Print 'After Update Message'