Handling errors in procedures and triggers on SQL Server

  • 27 November 2020
  • 0 replies
  • 73 views

Userlevel 2
Badge +10

Goal

 

This is a solution for default error handling on SQL Server. And how to add this solution in all your triggers and SQL procedures (tasks and subroutines) using dynamic control procedures. 

The solution is based on the article “Error and Transaction Handling in SQL Server” (Erland Sommarskog, 2015).   

 

When I started developing software on SQL Server, I missed an easy way to find the place where an error occurred.  I was used to DB2 where you could always find the sending program in the job log. It was easy to show the job log using a view and a table valued function.

 

On SQL Server this is a little different. When an error occurs you have some functions to get information about the error like ERROR_MESSAGE() and ERROR_PROCEDURE(). But if you don’t use a mechanism to handle the error, you don’t know where the error occurs. It could be in a procedure, in an instead of trigger or in one of the triggers down the stack.  

 

Solution

 

When I started looking for a solution, I found the article of Erland Sommarskog (see above). It’s maybe a little hard to read, but it describes all you need to know.  

The next step is to add this default error handling solution to the procedures and triggers generated with the Software Factory. You can use dynamic control procedures to weave this functionality in your code. We have seen the power of this feature when adding ‘trace columns’ in the workshop.  

It’s best to create a control procedure for each ‘code group’ (e.g. error_handling_trigger), so the code is also generated when you only generate one code group.

 

The control procedure for each code group, has two templates:

  • Begin_error_handling
  • End_error_handling

 

The begin_error_handling template in this example, defines a variable. You can use this variable in other templates to show a saved value when the error occurred.  The statement ”set xact_abort on” should not be used for triggers (is default for triggers). Finally the begin_error_handling starts the  “begin try” statement to be able to catch the error.   

 

-- Begin erro handling template

set xact_abort on

begin try

declare @_err_msg nvarchar(4000)

 

The begin_error_handling template should be placed immediately after the first template of the program object (e.g. after begin_task).  

Just before the last template (e.g. end_task) we assign the end_error_handling template.

In this example template we are going to catch the error, rollback the transaction (if any), report the error and re-raise the error.

 

-- End error handling

end try

begin catch

    if @@trancount > 0 rollback

    if dbo.get_user_property('var_log_message') = '1'   

    begin

        set @_err_msg = concat('ERROR in procedure: ', object_name(@@PROCID),

                               ' in line ', error_line(),

                               ' severity ', error_severity(),

                               ' state ', error_state(),

                               '. Message: ', coalesce(@_err_msg,'')

                               +  '-'  + error_message())

        exec tsf_send_message @_err_msg, null, 0;                    

    end

    ;throw

end catch

 

Note: In this example I use a user_property table to store user settings and a function get_user_property() to get the value of a property. This is not a default facility.   

 

When the user property ‘var_log_message’ = 1 (on), the error will be send to the screen as an information message (dbo.tsf_send_message with abort = 0).

The default for ‘var_log_message’ is 0 (=off), because the user should only see the last message and not all information messages from the procedures and triggers down the stack. Only when the user property is changed to 1 he will see the  information messages from the lowest point in the stack up to the starting procedure or trigger.    

 

The information messages, contain the name of the procedure or trigger  (object_name(@@PROCID)). Because the error is re-raised each time, the information messages will show the complete path from the first sending procedure or trigger to the last.

 

The example above only sends information to the screen when requested. You could also create your own procedure to handle the error. The end error handling template could look like this.  

 

end try

begin catch

   if @@trancount > 0 rollback

   exec my_error_handling_procedure @@procid, @_err_msg

   return

end catch

 

In the error handling procedure ‘my_error_handling_procedure’ you could also insert the starting error into a table with error messages. Because all re-raised errors are send by the error handling procedure itself, the one that isn’t is the first.

 

When you use this error handling mechanism you can even write less code. As in the documentation described, we normally handle errors like this.

 

if (error condition)

begin

   exec dbo.tsf_send_message ..... 1 --abort

   rollback

   return

end

 

Because we catch the errors now with the ‘begin try-end try, begin catch-end catch’, the rollback and return after the dbo.tsf_send_message will never be executed. So the code below will be enough.

 

if (error condition)

   exec dbo.tsf_send_message ..... 1 --abort

 

This error handling solution will have no effect on the performance, because it is only used when an error occurs.

I left out the source for the dynamic control procedures. Because there are no template parameters used, it’s nothing more than adding the begin and end template in the right place (prog_object_item). If you’re not familiar with dynamic control procedures, than let me know.

 

If you reached it to this point, than I like to thank you for reading. I hope this will make your life as developer a little bit easier.


0 replies

Be the first to reply!

Reply