September 21, 2022 at 10:00 am
Stored procedure OUTER_TEST calls stored procedure INNER_TEST.
If an error in INNER_TEST, how do I capture the error message (to store in a table). When I have a try-catch in OUTER_TEST, I can only access a later error message and not the initial one from INNER_TEST?
September 21, 2022 at 10:16 am
Try out the following code. You will see that the error generated in the first proc is captured in the second.
CREATE OR ALTER PROCEDURE dbo.ErrorTest1
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
SELECT 1 / 0;
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;
END;
GO
CREATE OR ALTER PROCEDURE dbo.ErrorTest2
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
EXEC dbo.ErrorTest1;
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;
END;
GO
EXEC dbo.ErrorTest2;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 21, 2022 at 6:31 pm
It might be a better option - if using TRY/CATCH - to THROW the error instead of using RAISERROR.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 22, 2022 at 8:02 am
It might be a better option - if using TRY/CATCH - to THROW the error instead of using RAISERROR.
I'd be interested to hear your reasoning.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 17, 2022 at 11:23 am
This was removed by the editor as SPAM
October 17, 2022 at 11:26 am
zoah666 wrote:Stored procedure OUTER_TEST calls stored procedure INNER_TEST.
If an error in INNER_TEST, how do I capture the error message (to store in a table). When I have a try-catch in OUTER_TEST, I can only access a later error message and not the initial one from INNER_TEST
I got this,..
What, exactly, have you got?
Did you understand my code? A very simple modification to it would achieve your desired outcome.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 17, 2022 at 8:12 am
Jeffrey Williams wrote:It might be a better option - if using TRY/CATCH - to THROW the error instead of using RAISERROR.
I'd be interested to hear your reasoning.
This is the main one that sticks out to me
Raiserror >Does not honor SET XACT_ABORT.
Throw > Transactions will be rolled back if SET XACT_ABORT is ON.
----------------------------------------------------
November 17, 2022 at 5:17 pm
Jeffrey Williams wrote:It might be a better option - if using TRY/CATCH - to THROW the error instead of using RAISERROR.
I'd be interested to hear your reasoning.
Using THROW will save all that coding for getting the error values. So 1 line instead of 7. Your code would just look like this
CREATE OR ALTER PROCEDURE dbo.ErrorTest1
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
SELECT 1 / 0;
END TRY
BEGIN CATCH
THROW;
END CATCH;
END;
GO
CREATE OR ALTER PROCEDURE dbo.ErrorTest2
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
EXEC dbo.ErrorTest1;
END TRY
BEGIN CATCH
THROW;
END CATCH;
END;
GO
EXEC dbo.ErrorTest2;
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply