Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Using try catch in SQL Server stored procedures


A great new option that was added in SQL Server 2005 was the ability to use the Try..Catch paradigm that exists in other development languages.  Doing error handling in SQL Server has not always been the easiest thing, so this option definitely makes it much easier to code for and handle errors.


If you are not familiar with the Try...Catch paradigm it is basically two blocks of code with your stored procedures that lets you execute some code, this is the Try section and if there are errors they are handled in the Catch section. 

Let's take a look at an example of how this can be done.  As you can see we are using a basic SELECT statement that is contained within the TRY section, but for some reason if this fails it will run the code in the CATCH section and return the error information.

CREATE PROCEDURE dbo.uspTryCatchTest
    SELECT 1/0
     ,ERROR_SEVERITY() AS ErrorSeverity
     ,ERROR_STATE() AS ErrorState
     ,ERROR_PROCEDURE() AS ErrorProcedure
     ,ERROR_LINE() AS ErrorLine
     ,ERROR_MESSAGE() AS ErrorMessage;


Last Update: 3/24/2009

More SQL Server Solutions

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Wednesday, March 15, 2017 - 8:34:37 AM - Unknown Back To Top


 Thanx nice post

Friday, December 23, 2016 - 4:17:53 PM - Norbert Muth Back To Top


you should include an example, how to throw an exeption within the try block and how and why to throw the exception further in the catch block.






Friday, September 09, 2016 - 10:23:25 PM - Akahay Bairagi Back To Top


 Very simple explanation and useful..

Can I get info on do what is that and why are we using it.

Saturday, July 09, 2016 - 1:07:30 AM - Eli Nieves Back To Top


 Awesome information!

Monday, February 01, 2016 - 5:23:12 AM - Bikash Back To Top

 Nice ! helpful


Learn more about SQL Server tools