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?

Answer 1

See here

 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

Answer 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