How to add a Try/Catch to SQL Stored Procedure

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

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

Advertisement

Answer