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?
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.
Instead of wrapping all of your code in a try catch block you basically add what's called a handler to your procedure. Typically you would want to stop executing, and in MySQL that is managed through an exit handler . Depending on the use case you may want to rollback or log the event when the handler is called.
Return value returns the Stored Procedure Name if an error occurs in a Stored Procedure or trigger and the catch block is called. It returns NULL if the error did not occur within a Stored Procedure or trigger or it isb called outside the scope of a CATCH block. First we create a Stored Procedure.
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
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)
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
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.
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 . . .
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With