Red Gate forums :: View topic - Trouble testing an expected sproc failure
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Test
SQL Test forum

Trouble testing an expected sproc failure

Search in SQL Test forum
Post new topic   Reply to topic
Jump to:  
Author Message
AdamY



Joined: 15 Oct 2010
Posts: 37

PostPosted: Tue Apr 02, 2013 5:48 pm    Post subject: Trouble testing an expected sproc failure Reply with quote

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:
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:
DECLARE @rc INT
EXEC @rc = dbo.uspMakeError
PRINT @rc

Quote:
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:
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:
Quote:
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:
Quote:
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>
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1112

PostPosted: Tue Apr 02, 2013 6:32 pm    Post subject: Reply with quote

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

https://groups.google.com/forum/?fromgroups#!forum/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
Back to top
View user's profile Send private message Send e-mail
AdamY



Joined: 15 Oct 2010
Posts: 37

PostPosted: Tue Apr 02, 2013 7:48 pm    Post subject: Moved to tSQLt Google Group Reply with quote

Thanks. This has been posted at:


http://groups.google.com/forum/#!topic/tsqlt/m4-SV4lfI9U
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group