How to add a Try Catch to SQL Stored Procedure

0 votes


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?

Sep 14, 2022 in Database by Kithuzzz
• 38,020 points
1,060 views

1 answer to this question.

0 votes

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
answered Sep 16, 2022 by narikkadan
• 63,720 points

Related Questions In Database

0 votes
0 answers

What is the syntax to drop a Stored Procedure in SQL Server 2000?

In SQL Server 2000, how do you ...READ MORE

Aug 25, 2022 in Database by Kithuzzz
• 38,020 points
555 views
0 votes
1 answer

How to schedule a job for SQL query to run daily?

down voteaccepted Expand the SQL Server Agent node ...READ MORE

answered Oct 25, 2018 in Database by Frankie
• 9,830 points
10,126 views
0 votes
2 answers

How to select the nth row in a SQL database table?

SELECT * FROM ( SELECT ID, NAME, ROW_NUMBER() ...READ MORE

answered Apr 23, 2020 in Database by anand
• 140 points
25,948 views
0 votes
1 answer

How to schedule a job for sql query to run daily?

Hii, To schedule a job for sql query ...READ MORE

answered May 4, 2020 in Database by Niroj
• 82,880 points
3,974 views
0 votes
1 answer

How do I UPDATE from a SELECT in SQL Server?

MERGE INTO YourTable T USING ...READ MORE

answered Feb 3, 2022 in Database by Vaani
• 7,070 points
771 views
0 votes
0 answers

How do I UPDATE from a SELECT in SQL Server?

INSERT INTO Table (col1, col2, col3) SELECT col1, ...READ MORE

Feb 4, 2022 in Database by Vaani
• 7,070 points
470 views
0 votes
1 answer

What is a stored procedure?

A stored procedure is a set of ...READ MORE

answered Feb 4, 2022 in Database by Neha
• 9,060 points
997 views
0 votes
1 answer

LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

At the top level there are mainly ...READ MORE

answered Feb 4, 2022 in Database by Neha
• 9,060 points
1,939 views
0 votes
1 answer

How to Execute SQL Server Stored Procedure in SQL Developer?

You don't need EXEC clause. Simply use: proc_name ...READ MORE

answered Sep 16, 2022 in Database by narikkadan
• 63,720 points
1,990 views
0 votes
1 answer

How to calculate percentage with a SQL statement

The following has passed my tests, and ...READ MORE

answered Sep 12, 2022 in Database by narikkadan
• 63,720 points
5,677 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP