Trouble testing an expected sproc failure

Easy unit testing for databases in SQL Server Management Studio

Moderators: JonathanWatts, David Atkinson, Anu Deshpande, Michelle Taylor, David Priddle

Trouble testing an expected sproc failure

Postby AdamY » Tue Apr 02, 2013 5:48 pm

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:
Code: Select all
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:
Code: Select all
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):
Code: Select all
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>
AdamY
 
Posts: 41
Joined: Fri Oct 15, 2010 8:24 pm

Postby David Atkinson » Tue Apr 02, 2013 6:32 pm

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
David Atkinson
 
Posts: 1124
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Moved to tSQLt Google Group

Postby AdamY » Tue Apr 02, 2013 7:48 pm

AdamY
 
Posts: 41
Joined: Fri Oct 15, 2010 8:24 pm


Return to SQL Test

Who is online

Users browsing this forum: No registered users and 0 guests