Implement Error Handling

(This is part of the Study Guide series, 70-457)

Microsoft’s Measured Skill description: This objective may include but is not limited to: implement try/catch/throw; use set based rather than row based logic; transaction management

What I see:

·         try/catch/throw

TRY/CATCH/THROW

                There are times that as a database developer you may want to catch errors and handle them accordingly.  This could include just letting them silently fail, or logging the parameters of the error, or re-throwing an error.  SQL Server allows us to do this very operation with the TRY…CATCH block.  If the code inside the TRY block throws an error, the CATCH block will be executed to handle the aforementioned error.  Below is an example:

begin try

       select 1/0;

end try

begin catch

       select

              error_message() as error_message,

              error_number() as error_number,

              error_severity() as error_severity,

              error_state() as error_state,

              error_line() as error_line

end catch

I use a blatant error (dividing by zero) to shift execution to the CATCH block, which simply selects the specific error parameters for viewing.  The built-in system functions of the CATCH block make available the specific (and appropriately named) portions of the error.  They are extremely useful if you do want to get the finer view of what happened to cause the CATCH block to execute.

The THROW statement is the successor to the RAISERROR() function.  It allows us to do just that:  THROW errors.  The syntax is as follows:

throw 50001, 'My Example Error Message', 1;

If the THROW statement is within a CATCH block, then parameters don’t need to be supplied:

begin try

       select 1/0;

end try

begin catch

       throw;

end catch

This allows us to re-THROW the error that caused the CATCH block to execute.

References

·         BOL reference on TRY…CATCH

·         BOL reference on THROW

If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.