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?

5 Answers

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 

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 

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

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 

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)>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>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;