How to add a Try/Catch to SQL Stored Procedure

Solution 1:

See TRY...CATCH (Transact-SQL)

 CREATE PROCEDURE [dbo].[PL_GEN_PROVN_NO1]        
       @GAD_COMP_CODE  VARCHAR(2) =NULL, 
       @@voucher_no numeric =null output 
       AS         
   BEGIN  

     begin try 
         -- your proc code
     end try

     begin catch
          -- what you want to do in catch
     end catch    
  END -- proc end

Solution 2:

Transact-SQL is a bit more tricky that C# or C++ try/catch blocks, because of the added complexity of transactions. A CATCH block has to check the xact_state() function and decide whether it can commit or has to rollback. I have covered the topic in my blog and I have an article that shows how to correctly handle transactions in with a try catch block, including possible nested transactions: Exception handling and nested transactions.

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(),
                 @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
        return;
    end catch   
end

Solution 3:

Error-Handling with SQL Stored Procedures

TRY/CATCH error handling can take place either within or outside of a procedure (or both). The examples below demonstrate error handling in both cases.

If you want to experiment further, you can fork the query on Stack Exchange Data Explorer.

_{(This uses a temporary stored procedure... we can't create regular SP's on SEDE, but the functionality is the same.)}

--our Stored Procedure
create procedure #myProc as --we can only create #temporary stored procedures on SEDE. 
  begin
    BEGIN TRY
      print 'This is our Stored Procedure.'
      print 1/0                          --<-- generate a "Divide By Zero" error.
      print 'We are not going to make it to this line.'
    END TRY

    BEGIN CATCH
      print 'This is the CATCH block within our Stored Procedure:'
          + ' Error Line #'+convert(varchar,ERROR_LINE())
          + ' of procedure '+isnull(ERROR_PROCEDURE(),'(Main)')
      --print 1/0                        --<-- generate another "Divide By Zero" error.
        -- uncomment the line above to cause error within the CATCH ¹ 
    END CATCH
  end
go

--our MAIN code block:
BEGIN TRY
  print 'This is our MAIN Procedure.'
  execute #myProc  --execute the Stored Procedure
      --print 1/0                        --<-- generate another "Divide By Zero" error.
        -- uncomment the line above to cause error within the MAIN Procedure ²
  print 'Now our MAIN sql code block continues.'
END TRY

BEGIN CATCH
  print 'This is the CATCH block for our MAIN sql code block:'
          + ' Error Line #'+convert(varchar,ERROR_LINE())
          + ' of procedure '+isnull(ERROR_PROCEDURE(),'(Main)')
END CATCH

Here's the result of running the above sql as-is:

This is our MAIN Procedure.
This is our Stored Procedure.
This is the CATCH block within our Stored Procedure: Error Line #5 of procedure #myProc
Now our MAIN sql code block continues.

¹ Uncommenting the "additional error line" from the Stored Procedure's CATCH block will produce:

This is our MAIN procedure.
This is our Stored Procedure.
This is the CATCH block within our Stored Procedure: Error Line #5 of procedure #myProc
This is the CATCH block for our MAIN sql code block: Error Line #13 of procedure #myProc

² Uncommenting the "additional error line" from the MAIN procedure will produce:

This is our MAIN Procedure.
This is our Stored Pprocedure.
This is the CATCH block within our Stored Procedure: Error Line #5 of procedure #myProc
This is the CATCH block for our MAIN sql code block: Error Line #4 of procedure (Main)

Use a single procedure for error handling

On topic of stored procedures and error handling, it can be helpful (and tidier) to use a single, dynamic, stored procedure to handle errors for multiple other procedures or code sections.

Here's an example:

--our error handling procedure
create procedure #myErrorHandling as
  begin
    print ' Error #'+convert(varchar,ERROR_NUMBER())+': '+ERROR_MESSAGE()  
    print ' occurred on line #'+convert(varchar,ERROR_LINE())
         +' of procedure '+isnull(ERROR_PROCEDURE(),'(Main)')
    if ERROR_PROCEDURE() is null       --check if error was in MAIN Procedure
      print '*Execution cannot continue after an error in the MAIN Procedure.'
  end
go

create procedure #myProc as     --our test Stored Procedure
  begin
    BEGIN TRY
      print 'This is our Stored Procedure.'
      print 1/0                       --generate a "Divide By Zero" error.
      print 'We will not make it to this line.'
    END TRY
    BEGIN CATCH
     execute #myErrorHandling
    END CATCH
  end
go

BEGIN TRY                       --our MAIN Procedure
  print 'This is our MAIN Procedure.'
  execute #myProc                     --execute the Stored Procedure
  print '*The error halted the procedure, but our MAIN code can continue.'
  print 1/0                           --generate another "Divide By Zero" error.
  print 'We will not make it to this line.'
END TRY
BEGIN CATCH
  execute #myErrorHandling
END CATCH

Example Output: _{^{(This query can be forked on SEDE here.)}}

This is our MAIN procedure.
This is our stored procedure.
 Error #8134: Divide by zero error encountered.
 occurred on line #5 of procedure #myProc
*The error halted the procedure, but our MAIN code can continue.
 Error #8134: Divide by zero error encountered.
 occurred on line #5 of procedure (Main)
*Execution cannot continue after an error in the MAIN procedure.

Documentation:

In the scope of a TRY/CATCH block, the following system functions can be used to obtain information about the error that caused the CATCH block to be executed:

• ERROR_NUMBER() returns the number of the error.
• ERROR_SEVERITY() returns the severity.
• ERROR_STATE() returns the error state number.
• ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
• ERROR_LINE() returns the line number inside the routine that caused the error.
• ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

^(Source)

Note that there are two types of SQL errors: Terminal and Catchable. TRY/CATCH will [obviously] only catch the "Catchable" errors. This is one of a number of ways of learning more about your SQL errors, but it probably the most useful.

It's "better to fail now" (during development) compared to later because, as Homer says . . .

Solution 4:

yep - you can even nest the try catch statements as:

BEGIN TRY
SET @myFixDte = CONVERT(datetime, @myFixDteStr,101)
END TRY
BEGIN CATCH
    BEGIN TRY
        SET @myFixDte = CONVERT(datetime, @myFixDteStr,103)
END TRY
BEGIN CATCH
    BEGIN TRY
        SET @myFixDte = CONVERT(datetime, @myFixDteStr,104)
    END TRY
    BEGIN CATCH
        SET @myFixDte = CONVERT(datetime, @myFixDteStr,105)
    END CATCH
END CATCH END CATCH