Clash Royale CLAN TAG#URR8PPP

How to continue executing a stored procedure after error is logged in the CATCH block

I've a requirement when a record fails to insert a record into the table, error should be caught in the CATCH block and stored procedure should not stop because of that error.

Below is the scenario:

CREATE PROCEDURE [dbo].[Pkg_StoredProcedure]
@Parameter1 NVARCHAR(20),
@Parameter2 INT,
@Parameter3 INT
AS
SET NOCOUNT ON;

BEGIN TRY
BEGIN TRAN T1
INSERT INTO Table1
SELECT * FROM Table2

COMMIT TRAN T1

END TRY
BEGIN CATCH

ROLLBACK TRAN T1

DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

INSERT INTO [ETL_ErrorLog]
SELECT DISTINCT
column1, column2, column3, column4,
@ErrorMessage + '(' + CONVERT(VARCHAR, @ErrorSeverity ) + '/' +
CONVERT (VARCHAR, @ErrorState)+ ')' as 'ErrorMessage'

END CATCH
GO


2 Answers
2

In a scenario like this I would try to separate out the code, so that when the error occurs the entire process isn't interrupted

In other words the code:

INSERT INTO Table1
SELECT * FROM Table2

COMMIT TRAN T1


I'm assuming this is not the actual code?
Could you move this section, into another stored procedure, (with the try catch) and run a loop that calls this new procedure? Then when the error occurs the "called" procedure will fail but not the "calling" procedure.
Error will be logged, loop continues

The try / catch construct is there to allow you control on exceptions instead of the default exit handler (meaning, abrupt procedure termination).

try / catch

Whatever code is after the END CATCH would be executed if you are not exiting the procedure from within the try / catch blocks.

END CATCH

try / catch

Note that in the example above you are triggering the error handling mechanism BEFORE the insert. As such, I suspect you are not seeing anything in your table [ELT_ErrorLog].

[ELT_ErrorLog]

RAISERROR

print 'Hi there'

END CATCH

try-catch

RAISERROR

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.