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.

begin tran
	begin try
		print 'first try'  -- executed
		select 6/0;
		print 'first try after error' -- we never get here, not executed!
		commit transaction  -- if we got here then commit
	end try
	begin catch
		print 'inside first catch!'
		SELECT   
			ERROR_NUMBER() AS ErrorNumber  
			,ERROR_MESSAGE() AS ErrorMessage;
		rollback transaction 
		print 'error. transaction rolled back!'
	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.

first try
-----------
(0 rows affected)
inside first catch!
ErrorNumber ErrorMessage
----------- ---------------------------------------------
8134        Divide by zero error encountered.
(1 row affected)
error. transaction rolled back!

Here below is an improvement to our error message.

        DECLARE @ErrorMessage NVARCHAR(4000);  
        DECLARE @ErrorSeverity INT;  
        DECLARE @ErrorState INT;  
        SELECT @ErrorMessage = ERROR_MESSAGE();  
        SELECT @ErrorSeverity = ERROR_SEVERITY();  
        SELECT @ErrorState = ERROR_STATE();  
  
        RAISERROR (@ErrorMessage, -- Message text.  
                   @ErrorSeverity, -- Severity.  
                   @ErrorState -- State.  
                   ); 

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.

begin tran
	begin try
		print 'first try'  -- executed
		select 6/0;
		print 'first try after error' -- we never get here, not executed!
		commit transaction  -- if we got here then commit
	end try
	begin catch
		print 'inside first catch!'
        -- Echo error information to the caller. 
        declare @ErrorMessage NVARCHAR(4000);  
        declare @ErrorSeverity INT;  
        declare @ErrorState INT;  
        select @ErrorMessage = ERROR_MESSAGE();  
        select @ErrorSeverity = ERROR_SEVERITY();  
        select @ErrorState = ERROR_STATE();  
        RAISERROR (@ErrorMessage, -- Message text.  
                   @ErrorSeverity, -- Severity.  
                   @ErrorState -- State.  
                   );  
		rollback transaction 
		print 'error. transaction rolled back!'
	end catch
-- -------------------------------------------------------------------------
begin tran  -- NON-nested transaction; independent of the above transaction
	begin try
		print 'second try'  -- executed
		select 6/0;
		commit transaction  -- if we got here then commit
	end try
	begin catch
		print 'inside second catch!'
        -- Echo error information to the caller. 
        select @ErrorMessage = ERROR_MESSAGE();  
        select @ErrorSeverity = ERROR_SEVERITY();  
        select @ErrorState = ERROR_STATE();  
        RAISERROR (@ErrorMessage, -- Message text.  
                   @ErrorSeverity, -- Severity.  
                   @ErrorState -- State.  
                   );  
		rollback transaction 
		print 'error. transaction rolled back!'
	end catch

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

(0 rows affected)
inside first catch!
Msg 50000, Level 16, State 1, Line 17
Divide by zero error encountered.
error. transaction rolled back!
second try
-----------
(0 rows affected)
inside second catch!
Msg 50000, Level 16, State 1, Line 37
Divide by zero error encountered.
error. 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.

begin tran
	begin try
                declare @myInt INT = 0;
                declare @myErrorMessage varchar(400) = '';
		-- <do some work here with myInt!>
		if myInt = 1 
                    begin
                        set @myErrorMessage = 'Error. Value is: ' + cast(myInt as varchar(2));
                        RAISERROR(@myErrorMessage , 16,1)
                    end
                if myInt = 2
                    begin
                        set @myErrorMessage = 'Error. Value is two, and that means...';
                        RAISERROR(@myErrorMessage , 16,1)
                    end
		commit transaction  -- if we got here then commit
	end try
	begin catch
		
        -- Echo error information to the caller. 
        declare @ErrorMessage NVARCHAR(4000);  
        declare @ErrorSeverity INT;  
        declare @ErrorState INT;  
        select @ErrorMessage = ERROR_MESSAGE();  
        select @ErrorSeverity = ERROR_SEVERITY();  
        select @ErrorState = ERROR_STATE();  
        RAISERROR (@ErrorMessage, -- Message text.  
                   @ErrorSeverity, -- Severity.  
                   @ErrorState -- State.  
                   );  
		rollback transaction 
		print 'error. transaction rolled back!'
	end catch