How to test a TSQL stored procedure that updates a table (vs. returning a result set) using tSQLt

Question

Could tSQLt experts please chime in on the approach for testing a type of stored procedures that don't return anything, but perform field updates in a table? I understand how to test results returned from a function or an SP, but in case of an in-place update, how do I make the test run against a fake table vs. the actual table if the procedure has the table name hard-coded? The only way I can think of is to make the entire SP use dynamic SQL and pass the table name as a parameter, but that makes the code less readable and more brittle. Is there a better way? Below is a simple stored proc that looks at 2 other fields: TransactionDate and ENdOfDropDate, and sets the third field in the same table called "IsWithinAddDrop" to True or False depending on the outcome of the condition.

create table  tblT1
(
   ID [bigint] IDENTITY(1,1) NOT NULL,
   TransactionDate   [datetime2](7) NULL,
   EndOfDropDate      [datetime2](7) NULL, 
   IsWithinAddDrop [nvarchar](10) NULL
)

insert into tblT1 (TransactionDate, EndOfDropDate) values ('1/1/2016',  '2/1/2016')
insert into tblT1 (TransactionDate, EndOfDropDate) values ('2/1/2016',  '1/2/2016')
insert into tblT1 (TransactionDate, EndOfDropDate) values ('3/1/2016',  '3/1/2016')

create procedure spUpdateIsWithinAddDrop
as 
begin
    Update t1
        set t1.IsWithinAddDrop =
        (case
            when t1.TransactionDate <= t1.EndOfDropDate then 'True'
            else 'False'
        end)
        from tblT1 t1
end

exec spUpdateIsWithinAddDrop

The result is the updated column IsWithinAddDrop in the table that I want to test:

TransactionDate EndOfDropDate   IsWithinAddDrop
2016-01-01      2016-02-01      True
2016-02-01      2016-01-02      False
2016-03-01      2016-03-01      True

Thanks!

Answer 1

The solution is to start by mocking the table to isolate any dependencies (foreign keys etc.). Then add enough data to test all the cases you want to cover (see comments in the example below) and use tSQLt.AssertEqualsTable to compare the contents of the target table with a pre-defined set of expected rows after running the procedure under test.

if schema_id(N'StackModuleTests') is null
    exec tSQLt.NewTestClass @ClassName = N'StackModuleTests'
go

if objectpropertyex(object_id(N'[StackModuleTests].[test spUpdateIsWithinAddDrop example]'), N'IsProcedure') = 1
    drop procedure [StackModuleTests].[test spUpdateIsWithinAddDrop example]
go

create procedure [StackModuleTests].[test spUpdateIsWithinAddDrop example]
as
begin
    --! Start by faking the table that will be updated to isolate this test from any other dependencies
    exec tSQLt.FakeTable @TableName = 'dbo.tblT1' ;

    --! We expect spUpdateIsWithinAddDrop to set IsWithinAddDrop to TRUE only if
    --! TransactionDate is less than or equal to EndOfDropDate so we need the
    --! following tests:
    --! 
    --! Positive case where TransactionDate equals EndOfDropDate
    --! Positive case where TransactionDate less than EndOfDropDate
    --! Negative case where TransactionDate more than EndOfDropDate
    --! May want other tests to cover scenarios where either column is null
    --! Purists would say that this should one unit test for each case, personally
    --! I feel that as SQL is a set based language it is OK to combine all cases
    --! into a single test (also minimises all the setup)
    --!

    --! Assemble the data required for all test cases
    insert into tblT1 (TransactionDate, EndOfDropDate)
    values
          ('20160101', '20160101')
        , ('20160101', '20160102')
        , ('20160102', '20160101') ;

    --! What do we expect to see afterwards?
    create table #expected
    (
      TransactionDate [datetime2](7) null
    , EndOfDropDate [datetime2](7) null
    , IsWithinAddDrop [nvarchar](10) null
    )

    insert into #expected (TransactionDate, EndOfDropDate, IsWithinAddDrop)
    values
          ('20160101', '20160101', 'True')
        , ('20160101', '20160102', 'True')
        , ('20160102', '20160101', 'False') ;

    --! Act
    exec dbo.spUpdateIsWithinAddDrop ;

    --! Assert that the contents of tblT1 now match the #expected contents
    --! Notice that we ignore the ID column completely in this test because
    --! it has nothing to do with the object under test (spUpdateIsWithinAddDrop)
    exec tSQLt.AssertEqualsTable @Expected = N'#expected', @Actual = N'tblT1' ;
end
go

exec tSQLt.Run '[StackModuleTests].[test spUpdateIsWithinAddDrop example]';

Hope this explains the approach sufficiently but if not please do seek further clarification.