A few posts ago we posted an error template we got from from sqlhints.com. The post was called SQL Server Exception Handling Template.

We are going to re-visit that post and make some changes to the code to see what we get. For example, what if inside the transaction we were calling three stored procedures instead of just three statements. If each statement was in its own stored procedure, would the transaction roll back when it encountered an error in the second one? It is inside a transaction.

Consider the following code. The three stored procedures were previously created, each with one line of code in them.

USE [SqlHintsErrorHandlingDemo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ErrorHandlingTemplateSProc]
AS
BEGIN
    BEGIN TRY
        SET NOCOUNT ON
        SET XACT_ABORT ON
			--  Code Which Doesn't Require Transaction
        BEGIN TRANSACTION
            -- Code which Requires Transaction
			DELETE FROM dbo.Account;
			EXEC InsertAccount1
                        -- INSERT INTO dbo.Account(AccountId, Name , Balance) 
			-- VALUES(1, 'Account1', 10000)
			EXEC InsertAccountDup
                        -- INSERT INTO dbo.Account(AccountId, Name , Balance) 
			-- VALUES(1, 'Duplicate', 10000)
                        EXEC InsertAccount2
                        -- INSERT INTO dbo.Account(AccountId, Name , Balance) 
			-- VALUES(2, 'Account2', 20000) 
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 AND XACT_STATE() <> 0 
            ROLLBACK TRANSACTION
			PRINT '*************Error Detail****************'
			PRINT 'Error Number  :' + CAST(ERROR_NUMBER() AS VARCHAR)
			PRINT 'Error Severity:' + CAST(ERROR_SEVERITY() AS VARCHAR)
			PRINT 'Error State   :' + CAST(ERROR_STATE() AS VARCHAR)
			PRINT 'Error Line    :' + CAST(ERROR_LINE() AS VARCHAR)
			PRINT 'Error Message :' + ERROR_MESSAGE()
        -- THROW --RETHROW the ERROR, But I found that THROW caused its own error!
        -- Due to the error from THROW itself, perhaps RAISEERROR would work?
    END CATCH
	SELECT * FROM dbo.Account WITH(NOLOCK);
END
GO

*************Error Detail****************
Error Number  :2627
Error Severity:14
Error State   :1
Error Line    :7
Error Message :Violation of PRIMARY KEY constraint 'PK__Account__349DA5A647432891'. Cannot insert duplicate key in object 'dbo.Account'. The duplicate key value is (1).
AccountId   Name                                               Balance
----------- -------------------------------------------------- ---------------------

Return Value
------------
0

(1 row(s) affected)

There are no rows in the dbo.Account table because the transaction was rolled back. The second stored procedure did not execte because of the primary key constraint. The first stored procedure’s statement was rolled back. We know this because there are no rows in the dbo.Account table. If it was not rolled back there would be one row in the table, because this first stored procedure does not cause an error.

The SQL Server error is displayed thanks to our PRINT code in the CATCH block.

We did not use the THROW statement. We found that it itself causes an error. We may need to use RAISEERROR instead.