在游标内运行tsqlt断言

Question

我编写tsqlt是针对一个可以针对各种参数值运行的proc编写的。我最初构建了一个proc来填充假表，然后每个可能的值进行1 tsqlt测试(最后得到35个测试，每个测试都有效)。

我想要做的是将它们简化为一个测试(因为它们都在测试相同的功能--只是为了不同的值)。我想我可以用这样的光标做这件事：

---- 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;

但是，使用

EXEC proc name @ReviewId = @ReviewId;

生成一条消息，表示没有运行测试。如何起诉游标以减少测试次数？还是我应该考虑另一种方法？

Answer 1

最后，我在几个步骤中实现了最终目标: 1.将assert语句移出游标2。创建带有pass/fail记录的“大小写”临时表。

        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;

1. 在游标之外，我设置了一个新参数，如果它们存在就会失败，如果它们不存在，则接受“pass”。

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

1. 然后，我修改了断言，使其失败，如果@结果不是'Pass‘

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

**注一将以前的临时表和预期表改为可变表

Answer 2

我建议你写一个叫做参数化测试的东西。

tSQLt (目前)还没有本机支持，但是有一个简单的解决方法：

你通常从写一个测试开始。但是，不是硬编码相关的值，而是让它们成为过程的参数。(对于数据集，可以使用表参数。)

您还可以将这个过程命名为不是以"test“开头的东西(而是生活在同一个模式中)。

然后，根据实际情况编写一个真正的测试，每个测试由一行组成:参数化过程的执行。

这将导致比当前方法更容易理解的测试。另外，如果其中一个失败了，你马上就知道是哪一个。

顺便提一句:你总是想硬编码你的预期结果。您当前的代码非常复杂。您希望最小化测试本身可能出错的事情。实际上，你的目标应该是可以一目了然地理解的测试。