SQL Server Exception Handling Part 14

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

This post is a continuation of our SQL Server project that uses exception handling, except that here we are simplifying the code by removing the stored procedures and the tables. Here we will look at just running a block of code that could be in a sql file or in a single stored procedure. In previous examples we considered a stored procedure that called another stored procedure or procedures.

If we get an error we need to roll back all of the code that exists inside the TRY block. In this simple example below, the error happens when we try to divide by zero. As soon as an error happens, the execution jumps to the CATCH block. We have a print and a select statement. Then we roll back the transaction. In this example there really is nothing to roll back. This is just a simple example.

Note that tran and transaction are equivalent.

1begin tran
2    begin try
3        print 'first try'  -- executed
4        select 6/0;
5        print 'first try after error' -- we never get here, not executed!
6        commit transaction  -- if we got here then commit
7    end try
8    begin catch
9        print 'inside first catch!'
10        SELECT  
11            ERROR_NUMBER() AS ErrorNumber  
12            ,ERROR_MESSAGE() AS ErrorMessage;
13        rollback transaction
14        print 'error. transaction rolled back!'
15    end catch

Here is the output in SSMS when we set it to Results to Text. I removed some of the blank lines and — for brevity.

1first try
2-----------
3(0 rows affected)
4inside first catch!
5ErrorNumber ErrorMessage
6----------- ---------------------------------------------
78134        Divide by zero error encountered.
8(1 row affected)
9error. transaction rolled back!

Here below is an improvement to our error message.

1DECLARE @ErrorMessage NVARCHAR(4000);  
2DECLARE @ErrorSeverity INT;  
3DECLARE @ErrorState INT;  
4SELECT @ErrorMessage = ERROR_MESSAGE();  
5SELECT @ErrorSeverity = ERROR_SEVERITY();  
6SELECT @ErrorState = ERROR_STATE();  
7 
8RAISERROR (@ErrorMessage, -- Message text.  
9           @ErrorSeverity, -- Severity.  
10           @ErrorState -- State.  
11           ); 

Multiple Separate Transactions

Non-nested transactions with a TRY…CATCH block can be handled with the following code example. Each transaction starts and ends on its own independent of the other one. Both transactions below have a divide by zero error.

1begin tran
2    begin try
3        print 'first try'  -- executed
4        select 6/0;
5        print 'first try after error' -- we never get here, not executed!
6        commit transaction  -- if we got here then commit
7    end try
8    begin catch
9        print 'inside first catch!'
10        -- Echo error information to the caller. 
11        declare @ErrorMessage NVARCHAR(4000);  
12        declare @ErrorSeverity INT;  
13        declare @ErrorState INT;  
14        select @ErrorMessage = ERROR_MESSAGE();  
15        select @ErrorSeverity = ERROR_SEVERITY();  
16        select @ErrorState = ERROR_STATE();  
17        RAISERROR (@ErrorMessage, -- Message text.  
18                   @ErrorSeverity, -- Severity.  
19                   @ErrorState -- State.  
20                   );  
21        rollback transaction
22        print 'error. transaction rolled back!'
23    end catch
24-- -------------------------------------------------------------------------
25begin tran  -- NON-nested transaction; independent of the above transaction
26    begin try
27        print 'second try'  -- executed
28        select 6/0;
29        commit transaction  -- if we got here then commit
30    end try
31    begin catch
32        print 'inside second catch!'
33        -- Echo error information to the caller. 
34        select @ErrorMessage = ERROR_MESSAGE();  
35        select @ErrorSeverity = ERROR_SEVERITY();  
36        select @ErrorState = ERROR_STATE();  
37        RAISERROR (@ErrorMessage, -- Message text.  
38                   @ErrorSeverity, -- Severity.  
39                   @ErrorState -- State.  
40                   );  
41        rollback transaction
42        print 'error. transaction rolled back!'
43    end catch

Here are the results in SSMS when you select Results to Text. I have removed all of the blank rows fro brevity.

1(0 rows affected)
2inside first catch!
3Msg 50000, Level 16, State 1, Line 17
4Divide by zero error encountered.
5error. transaction rolled back!
6second try
7-----------
8(0 rows affected)
9inside second catch!
10Msg 50000, Level 16, State 1, Line 37
11Divide by zero error encountered.
12error. transaction rolled back!

Boilerplate Code

Below is an example of some boilerplate code (and a little bit of custom code) that you might use in a project. Instead of just throwing an error by dividing by zero, I will pretend that we are doing some tests. We need to validate something in our data. When we do these tests we want to be able to report an error that has some information in it that he user can then use to further investigate the situation. For example you might be counting rows with a SELECT statement and you store that value in the myInt variable. You expect to have no duplicates so you expect myInt to stay at zero and you need to raise a critical error of myInt is 1 or 2. I did not include that code here in this example below because I want this to be simple boilerplate code.

1begin tran
2    begin try
3                declare @myInt INT = 0;
4                declare @myErrorMessage varchar(400) = '';
5        -- <do some work here with myInt!>
6        if myInt = 1 
7                    begin
8                        set @myErrorMessage = 'Error. Value is: ' + cast(myInt as varchar(2));
9                        RAISERROR(@myErrorMessage , 16,1)
10                    end
11                if myInt = 2
12                    begin
13                        set @myErrorMessage = 'Error. Value is two, and that means...';
14                        RAISERROR(@myErrorMessage , 16,1)
15                    end
16        commit transaction  -- if we got here then commit
17    end try
18    begin catch
19         
20        -- Echo error information to the caller. 
21        declare @ErrorMessage NVARCHAR(4000);  
22        declare @ErrorSeverity INT;  
23        declare @ErrorState INT;  
24        select @ErrorMessage = ERROR_MESSAGE();  
25        select @ErrorSeverity = ERROR_SEVERITY();  
26        select @ErrorState = ERROR_STATE();  
27        RAISERROR (@ErrorMessage, -- Message text.  
28                   @ErrorSeverity, -- Severity.  
29                   @ErrorState -- State.  
30                   );  
31        rollback transaction
32        print 'error. transaction rolled back!'
33    end catch

Series Navigation<< SQL Server Exception Handling Part 13

BeginCodingNow.com

for data analysts & software developers

for data analysts & software developers

SQL Server Exception Handling Part 14

Multiple Separate Transactions

Boilerplate Code