How to add a Try Catch to SQL Stored Procedure

Question

CREATE PROCEDURE  dbo   PL GEN PROVN NO1           GAD COMP CODE  VARCHAR 2   NULL     voucher no numeric  null output  AS          BEGIN       DECLARE  NUM NUMERIC      DECLARE  PNO  NUMERIC                                    SET  PNO   0      DECLARE  PNO1 NUMERIC     SET  PNO1 0   --  begin transaction       IF NOT EXISTS  select GLDC NEXT PRV NO                FROM   GLAS FINANCIAL DOCUMENTS                              WHERE  GLDC COMP CODE     GAD COMP CODE                            AND GLDC DOC CODE     JV        BEGIN                RAISERROR   Error in generating provision number    16 1                  -- ROLLBACK TRANSACTION     END ELSE SELECT  PNO ISNULL GLDC NEXT PRV NO 0  1 FROM   GLAS FINANCIAL DOCUMENTS           WHERE  GLDC COMP CODE     GAD COMP CODE         AND GLDC DOC CODE     JV    UPDATE  GLAS FINANCIAL DOCUMENTS         SET GLDC NEXT PRV NO    PNO          WHERE  GLDC COMP CODE     GAD COMP CODE         AND GLDC DOC CODE     JV    set   VOUCHER NO  PNO     --commit transaction  END   In this proc how can I handle try catch for exception

User · Answer

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

User · Answer

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  gt  0             rollback transaction usp my procedure name           raiserror   usp my procedure name   d   s   16  1   error   message            return      end catch    end

User · Answer

Create Proc usp mquestions         title  nvarchar 500     --0   tags  nvarchar max     --1   category  nvarchar 200     --2   ispoll  char 1     --3   descriptions  nvarchar max     --4                  AS    BEGIN TRY     BEGIN DECLARE  message varchar 1000    DECLARE  tempid bigint    IF  SELECT count id  from   xyz  WHERE title  title  gt 0  BEGIN SELECT  record already existed    END ELSE BEGIN                  if  id 0  begin  select  tempid  id from  xyz  where id  id   if  tempid is null  BEGIN          INSERT INTO xyz          entrydate updatedate          VALUES          GETDATE   GETDATE             SET  tempid   IDENTITY   END  END  ELSE  BEGIN  set  tempid  id  END  if  tempid gt 0  BEGIN       -- Updation of table begin--   UPDATE  tab questions set title  title  --0   tags  tags  --1   category  category  --2   ispoll  ispoll  --3   descriptions  descriptions  --4   status  status  --5  WHERE id  tempid   --9     IF  id 0  BEGIN  SET  message   success Record added successfully    convert varchar 10    tempid  END  ELSE  BEGIN  SET  message   success Record updated successfully     convert varchar 10    tempid   END  END  ELSE  BEGIN  SET  message   failed invalid request   convert varchar 10    tempid  END   END END  END TRY BEGIN CATCH     SET  message  failed    ERROR MESSAGE    END CATCH SELECT  message

User · Answer

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

User · Answer

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                          -- lt -- 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                        -- lt -- 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                        -- lt -- 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

[sql] How to add a Try/Catch to SQL Stored Procedure

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to try-catch