SQL Server Exception Handling Part 4

 in SQL Server  tagged SQL Server Exceptions by
This entry is part 5 of 15 in the series SQL Server Exceptions

SQL Server Exceptions

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.

view source
print?
1USE [SqlHintsErrorHandlingDemo]
2GO
3SET ANSI_NULLS ON
4GO
5SET QUOTED_IDENTIFIER ON
6GO
7ALTER PROCEDURE [dbo].[ErrorHandlingTemplateSProc]
8AS
9BEGIN
10    BEGIN TRY
11        SET NOCOUNT ON
12        SET XACT_ABORT ON
13            --  Code Which Doesn't Require Transaction
14        BEGIN TRANSACTION
15            -- Code which Requires Transaction
16            DELETE FROM dbo.Account;
17            EXEC InsertAccount1
18                        -- INSERT INTO dbo.Account(AccountId, Name , Balance)
19            -- VALUES(1, 'Account1', 10000)
20            EXEC InsertAccountDup
21                        -- INSERT INTO dbo.Account(AccountId, Name , Balance)
22            -- VALUES(1, 'Duplicate', 10000)
23                        EXEC InsertAccount2
24                        -- INSERT INTO dbo.Account(AccountId, Name , Balance)
25            -- VALUES(2, 'Account2', 20000)
26        COMMIT TRANSACTION
27    END TRY
28    BEGIN CATCH
29        IF @@TRANCOUNT > 0 AND XACT_STATE() <> 0
30            ROLLBACK TRANSACTION
31            PRINT '*************Error Detail****************'
32            PRINT 'Error Number  :' + CAST(ERROR_NUMBER() AS VARCHAR)
33            PRINT 'Error Severity:' + CAST(ERROR_SEVERITY() AS VARCHAR)
34            PRINT 'Error State   :' + CAST(ERROR_STATE() AS VARCHAR)
35            PRINT 'Error Line    :' + CAST(ERROR_LINE() AS VARCHAR)
36            PRINT 'Error Message :' + ERROR_MESSAGE()
37        -- THROW --RETHROW the ERROR, But I found that THROW caused its own error!
38        -- Due to the error from THROW itself, perhaps RAISEERROR would work?
39    END CATCH
40    SELECT * FROM dbo.Account WITH(NOLOCK);
41END
42GO
view source
print?
1*************Error Detail****************
2Error Number  :2627
3Error Severity:14
4Error State   :1
5Error Line    :7
6Error Message :Violation of PRIMARY KEY constraint 'PK__Account__349DA5A647432891'. Cannot insert duplicate key in object 'dbo.Account'. The duplicate key value is (1).
7AccountId   Name                                               Balance
8----------- -------------------------------------------------- ---------------------
9 
10Return Value
11------------
120
13 
14(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.

Series Navigation<< SQL Server Exception Handling Part 3SQL Server Exception Handling Part 5 >>