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>
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: [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: [3] Now create the SQL Test test sproc (assumes a "test" schema): [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.
Even if I remove the "BEGIN TRANSACTION" line in [dbo].[uspMakeError], the SQL Test test will still fail: