Using try catch in SQL Server stored procedures


MENU
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

 


<< Previous

Next >>



Comments For This Article




Monday, August 22, 2022 - 8:12:29 AM - ANTONIO CARLOS SEQUEIRA BONITO Back To Top (90395)
Try-Catch trap errors only inside the scope of sql server...many issues are not covered this engine like lost connections ( network errors), acess ( sistem security erros ) , sql components errors ( missing or troubled dlls ) . The best alternative to solve this question is discovering the trouble before it happens like make a function that detect and return a value thats allow the main procedure to go ahead or stop because this 'outside' problem.

Friday, May 24, 2019 - 3:27:29 PM - Greg Robidoux Back To Top (80199)

Hi Sandip,

I tried your code and it seemed to work for me.  The only thing I added was this after the CLOSE TESTCURSOR.

DEALLOCATE TESTCURSOR

I don't know the structure of your tables, so not sure if that is where the issue is.

Thursday, May 16, 2019 - 6:23:07 AM - SANDIP SHARMA Back To Top (80100)

Hi There,

I am trying to write some error handeling code in sql server but below code is not working while any conversion failed in converting date.

DECLARE @CREATE_DATE VARCHAR(20)
DECLARE @PERSON_NAME VARCHAR(255)
DECLARE @REGION VARCHAR(20)
DECLARE @SYNCOPERATION VARCHAR(255)
 
--DECLARATION OF CURSOR
DECLARE TESTCURSOR CURSOR FOR 
SELECT 
   CREATE_DATE,
   PERSON_NAME,
   REGION,
   GETDATE()
FROM TEST

OPEN TESTCURSOR

FETCH NEXT FROM TESTCURSOR INTO @CREATE_DATE, @PERSON_NAME, @REGION, @SYNCOPERATION

WHILE (@@FETCH_STATUS =0)
BEGIN
 
   BEGIN TRY  
      --INSERT INTO TEST_CLEAN
      SELECT 
         CONVERT(DATETIME,@CREATE_DATE,103) CREATE_DATE,
         @PERSON_NAME,
         @REGION,
         @SYNCOPERATION
   END TRY  
   BEGIN CATCH  
       -- Execute error retrieval routine.  
       INSERT INTO DB_ERRORS VALUES (SUSER_SNAME(),ERROR_NUMBER(), ERROR_STATE(), ERROR_SEVERITY(), ERROR_LINE(), ERROR_PROCEDURE(), ERROR_MESSAGE(), GETDATE(), @REGION );
   END CATCH;
 
   FETCH NEXT FROM TESTCURSOR INTO @CREATE_DATE, @PERSON_NAME, @REGION, @SYNCOPERATION
 
END
 
CLOSE TESTCURSOR

Input Data

CREATE_DATE PERSON_NAME REGION (No column name)
30/07/2019 23:59 Anna Andreadi West      2019-05-16 15:52:30.340
OCT-30-2019      Chuck Magee East        2019-05-16 15:52:30.340
30/07/2019 23:59 Kelly Williams Central  2019-05-16 15:52:30.340
30/07/2019       Cassandra Brandow South 2019-05-16 15:52:30.340

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

 

 Thanx nice post

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

 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 9, 2016 - 10:23:25 PM - Akahay Bairagi Back To Top (43293)

 

 Very simple explanation and useful..

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

Saturday, July 9, 2016 - 1:07:30 AM - Eli Nieves Back To Top (41850)

 

 Awesome information!

Monday, February 1, 2016 - 5:23:12 AM - Bikash Back To Top (40562)

 Nice ! helpful

 














get free sql tips
agree to terms