Trouble testing an expected sproc failure

AdamY

I am new to SQL Test and I'm trying to test that a sproc will fail when expected and am having some problems. I am curious if anyone has a work-around or can tell me what I've done wrong.
From what I can tell, SQL Test stops executing the sproc when an error is raised, but SQL Server does not - which is why it works when our application calls the sproc, but not when SQL Test does. This causes 2 problems:
1. SQL Test thinks the sproc's return code is NULL.
2. SQL Test thinks there is an uncommitted transaction since it never runs the ROLLBACK in the sproc.

Here is the setup...

[1] Sproc to be tested:

CREATE PROCEDURE [dbo].[uspMakeError]
AS
SET NOCOUNT ON

DECLARE
    @RowCount   INT,
    @RetCode    INT,
    @TranLevel  INT,
    @ErrMsg     VARCHAR(2047),
    @ErrState   INT,
    @ErrSev     INT
    
BEGIN TRY
    SET @TranLevel = @@TRANCOUNT
    SET @RetCode = 0

    BEGIN TRANSACTION
    
    IF 1 = 1  -- Some condition is met that should raise an error.
        BEGIN
            SET @ErrMsg = 'My error message.'
            RAISERROR(@ErrMsg, 11, 1)  -- Send control to CATCH block.
        END
END TRY

BEGIN CATCH
    SET @RetCode = -1
    SET @ErrState = ERROR_STATE()
    SET @ErrSev = ERROR_SEVERITY()
    
    -- Update error message.
    SET @ErrMsg = ISNULL(ERROR_MESSAGE(), 'No error message available.')
          /* Only show sproc name if error occurred in a child sproc. */
        + CASE WHEN ISNULL(OBJECT_NAME(@@PROCID), '') <> ISNULL(ERROR_PROCEDURE(), '') THEN ' Procedure: ' + ERROR_PROCEDURE() + '.' ELSE '' END
        + ' Error: ' + CAST(ISNULL(ERROR_NUMBER(), 0) AS VARCHAR) + '.'
        + ' Line: ' + CAST(ISNULL(ERROR_LINE(), 0) AS VARCHAR) + '.'

    RAISERROR(@ErrMsg , @ErrSev, @ErrState)  -- !! THIS IS WHERE SQL TEST STOPS !!

    IF @@TRANCOUNT > @TranLevel
        ROLLBACK TRANSACTION
END CATCH

IF @@TRANCOUNT > @TranLevel
    COMMIT TRANSACTION

RETURN @RetCode

[2] Test in SQL Server. Run this code in SSMS - it works fine, the @rc value is correct and there are no transaction state errors:

DECLARE @rc INT
EXEC @rc = dbo.uspMakeError
PRINT @rc

Msg 50000, Level 11, State 1, Procedure uspMakeError, Line 36
My error message. Error: 50000. Line: 22.
-1

[3] Now create the SQL Test test sproc (assumes a "test" schema):

CREATE PROCEDURE [test].[test Make sure a sproc fails]
AS
BEGIN
    DECLARE @RC INT

    BEGIN TRY
        EXEC @RC = dbo.uspMakeError
    END TRY
    BEGIN CATCH
    END CATCH

EXEC tSQLt.AssertEquals @Expected = -1, @Actual = @RC, @Message = 'Oops.'
  
END

[4] Now run the "test Make sure a sproc fails" test in SQL Test. This is the output:

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
[test].[test Make sure a sproc fails] failed: Oops. Expected: <-1> but was: <NULL>

You can see 2 problems. The transaction count error and notice that SQL Test thinks the @RC value is NULL instead of -1.

Even if I remove the "BEGIN TRANSACTION" line in [dbo].[uspMakeError], the SQL Test test will still fail:

Test Procedure: [MyDB].[test].[test Make sure a sproc fails] on MyServer
[test].[test Make sure a sproc fails] failed: Oops. Expected: <-1> but was: <NULL>

David Atkinson

Questions to do with the underlying tSQLt framework are best placed on the GoogleGroup forum:

https://groups.google.com/forum/?fromgr ... orum/tsqlt

To access the tSQLt experts I'd recommend that you post you question there, maybe referencing this post to avoid duplication.

kind regards

David Atkinson
Red Gate

AdamY

Thanks. This has been posted at:


http://groups.google.com/forum/#!topic/ ... 4-SV4lfI9U