Trouble testing an expected sproc failure
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:
Even if I remove the "BEGIN TRANSACTION" line in [dbo].[uspMakeError], the SQL Test test will still fail:
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
[3] Now create the SQL Test test sproc (assumes a "test" schema):Msg 50000, Level 11, State 1, Procedure uspMakeError, Line 36
My error message. Error: 50000. Line: 22.
-1
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:
You can see 2 problems. The transaction count error and notice that SQL Test thinks the @RC value is NULL instead of -1.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>
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>
Comments
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
Product Manager
Redgate Software
http://groups.google.com/forum/#!topic/ ... 4-SV4lfI9U