Learn more about SQL Server tools

mssqltips logo
giveaway
 

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



By:
Overview

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.

Explanation

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
AS
BEGIN TRY
    SELECT 1/0
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber
     ,ERROR_SEVERITY() AS ErrorSeverity
     ,ERROR_STATE() AS ErrorState
     ,ERROR_PROCEDURE() AS ErrorProcedure
     ,ERROR_LINE() AS ErrorLine
     ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

 






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    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



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

 Hallo,

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.

thank's

 

Norbert

 

 


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