Running tsqlt assert inside a cursor

Question

I'm writing tsqlt against a proc that can be run against various parameter values. I initially built a proc that populates fake tables - and then 1 tsqlt test per possible value (ended up with 35 tests and they each worked).

What I would like to do is reduce these into 1 test (since they are all really testing the same functionality - just for different values). I thought I could do this with a cursor like so:

---- Declare Sproc  Variables
        DECLARE @ReviewId INT;
        DECLARE @SourceId INT = 1;

        CREATE TABLE #Present 
            (
              SubmissionReviewId INT ,
              username VARCHAR(50)
            );

        CREATE TABLE #Expected
            (
              SubmissionReviewId INT ,
              username VARCHAR(50)
            );

--Create Cursor to loop through each active value
        DECLARE review_id CURSOR
        FOR
            SELECT  ReviewId
            FROM    reftype.Rev
            WHERE   IsActive = 1;

        OPEN review_id;

        FETCH NEXT FROM review_id 
                    INTO @ReviewId;

        WHILE @@FETCH_STATUS = 0
            BEGIN  

--Setup Fake Data according to the specified test condition
                EXEC ut_DataSetupProc @ReviewId = @ReviewId;

-- Run set cutover Sproc
                EXEC Procbeing Tested @ReviewId = @ReviewId,
                    @SourceId = 1, @Username = 'blah';

-- Confirm appropriate review is present in Submission Review Active
                DELETE  FROM #Present;

                DELETE  FROM #Expected;

                INSERT  INTO #Present
                        SELECT  SubmissionReviewId ,
                                LastModifiedBy
                        FROM    review.SubmissionReviewActive
                        ORDER BY SubmissionReviewId ,
                                LastModifiedBy;

/**********************Create table holding expected values***************************/


                INSERT  INTO #Expected
--This confirms active reviews that belong to other sections/sources remain unaffected
                        SELECT  SubmissionReviewId ,
                                LastModifiedBy
                        FROM    review.SubmissionReviewActive
                        WHERE   ( ReviewId != @ReviewId )
                                OR ( SourceId != @SourceId )
                        UNION

                        SELECT  sra.SubmissionReviewId ,
                                sra.LastModifiedBy
                        FROM    review.SubmissionReviewActive sra
                                JOIN review.SubmissionReviewFutureActive srfa ON srfa.IssuerId = sra.IssuerId
                                                              AND srfa.ReviewId = sra.ReviewId
                                                              AND srfa.Version < sra.Version
                        WHERE   sra.ReviewId = @ReviewId
                                AND sra.SourceId = @SourceId
                        UNION

                        SELECT  srfa.SubmissionReviewId ,
                                'jmarina' AS LastModifiedBy
                        FROM    review.SubmissionReviewFutureActive srfa
                                JOIN review.SubmissionReviewActive sra ON srfa.IssuerId = sra.IssuerId
                                                              AND srfa.ReviewId = sra.ReviewId
                                                              AND srfa.Version > sra.Version
                        WHERE   sra.ReviewId = @ReviewId
                                AND srfa.SourceId = @SourceId
                        UNION 

                        SELECT  srfa.SubmissionReviewId ,
                                'blah' AS LastModifiedBy
                        FROM    review.SubmissionReviewFutureActive srfa
                        WHERE   srfa.ReviewId = @ReviewId
                                AND srfa.SourceId = @SourceId
                                AND srfa.IssuerId NOT IN (
                                SELECT  IssuerId
                                FROM    review.SubmissionReviewActive
                                WHERE   ReviewId = @ReviewId
                                        AND SourceId = @SourceId )
                        UNION 

                        SELECT  sra.SubmissionReviewId ,
                                sra.LastModifiedBy
                        FROM    review.SubmissionReviewActive sra
                        WHERE   sra.ReviewId = @ReviewId
                                AND sra.SourceId = @SourceId
                                AND IssuerId NOT IN (
                                SELECT  IssuerId
                                FROM    review.SubmissionReviewFutureActive
                                WHERE   ReviewId = @ReviewId
                                        AND SourceId = @SourceId )
                        ORDER BY SubmissionReviewId ,
                                LastModifiedBy;


/*************************************************************/


                EXEC tSQLt.AssertEqualsTable @Expected = '#Expected',
                    @Actual = '#Present', @Message = N'', -- nvarchar(max)
                    @FailMsg = N'Active Status is not a match'; -- nvarchar(max)



                FETCH NEXT FROM review_id
                                        INTO @ReviewId;
            END;
        CLOSE review_id;
        DEALLOCATE review_id;

        DROP TABLE #Expected;
        DROP TABLE #Present;

    END;

However, running this using

EXEC proc name @ReviewId = @ReviewId;

yields a message saying no tests were run. How can I sue a cursor to reduce my number of tests? Or is there another approach I should consider?

Answer 1

In the end I achieved the end goal in a couple of steps: 1. Move the assert statement outside of the cursor 2. Created 'cased' temp table with pass/fail records

        INSERT  INTO #ActualAssert
                SELECT  p.SubmissionReviewId,e.SubmissionReviewId,
                        CASE WHEN ( e.SubmissionReviewId IS NULL
                                    OR p.SubmissionReviewId IS NULL
                                  ) THEN 'Fail'
                             ELSE 'Pass'
                        END
                FROM    @Present p
                        LEFT JOIN @Expected e ON e.SubmissionReviewId = p.SubmissionReviewId
                UNION
                SELECT  p.SubmissionReviewId,e.SubmissionReviewId ,
                        CASE WHEN ( e.SubmissionReviewId IS NULL
                                    OR p.SubmissionReviewId IS NULL
                                  ) THEN 'Fail'
                             ELSE 'Pass'
                        END
                FROM    @Present p
                        RIGHT JOIN @Expected e ON e.SubmissionReviewId = p.SubmissionReviewId;

3. Outside of the cursor I set up a new parameter that takes any fails if they exist or 'pass' if they don't

SET @Result = ( SELECT DISTINCT TOP 1
                        TestStatus
                FROM    #ActualAssert
                ORDER BY TestStatus ASC
              );

4. Then I modified the assert to fail if @result is anything other than 'Pass'

EXEC tSQLt.AssertEqualsString @Expected = N'Pass', -- nvarchar(max)
    @Actual = @Result, @Message = N''; -- nvarchar(max)

** A note I change previous present and expected temp tables into variable tables

Answer 2

I'd suggest you write something called a parameterized test.

tSQLt does not (yet) have native support for that, but there is an easy workaround:

You start by writing one of your tests normally. But instead of hardcoding the pertinent values, you make them parameters of the procedure. (For data sets, you can use table parameters.)

You also name that procedure something that doesn't start with "test" (but lives in the same schema).

Then you write one real test per actual case, each one consisting of one line: the execution of your parameterized procedure.

That will lead to tests that are a lot easier to understand than your current approach. And additionally, if one of them fails, you immediately know which.

As a side note: You always want to hardcode your expected results. Your current code is way complex. You want to minimize things that can go wrong in the test itself. Really, your goal should be tests that can be understood with one glance.