How to get output value from a stored procedure in case of error

Question

How to get output value from a stored procedure in case of error

Aleksei Galeev 40

There is a test SP which has one output parameter only:

IF OBJECT_ID('dbo.USP_SAMPLE_PROCEDURE', 'P') IS NOT NULL
   DROP PROCEDURE dbo.USP_SAMPLE_PROCEDURE;
GO

CREATE PROCEDURE USP_SAMPLE_PROCEDURE
( 
	@Param1 int OUTPUT 
)
AS
BEGIN
	SET XACT_ABORT, NOCOUNT ON;
	
	BEGIN TRY
		SELECT @Param1 = 123;
		;THROW 50001, 'Test error', 0;
	END TRY
    BEGIN CATCH
		SELECT @Param1 = 5;
		-- Echo error information to the caller.
		;THROW;
    END CATCH
END

As you can see, I set some value for the @Param1 in case of normal flow and in case of error.

What is interesting about my code is that I cannot get the value 5. Here is my call:

DECLARE @Param1 int;
BEGIN TRY
	EXEC dbo.USP_SAMPLE_PROCEDURE @Param1=@Param1 OUTPUT;
	SELECT @Param1 AS [FROM TRY]
END TRY
BEGIN CATCH
	SELECT @Param1 AS [FROM CATCH]
END CATCH

The result is NULL instead of 5. What am I doing wrong?

LiHongMSFT-4306 31,566 Reputation points

2023-07-04T06:55:56.8233333+00:00

Hi @Aleksei Galeev

As answered below, it is related to the logical processing steps.

If any answer helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

Accepted answer

1 additional answer

Your answer

LiHongMSFT-4306 31,566 Reputation points

2023-07-04T06:55:56.8233333+00:00

Hi @Aleksei Galeev

As answered below, it is related to the logical processing steps.

If any answer helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

Answer 1

Erland Sommarskog 121.4K MVP Volunteer Moderator

You are not doing anything wrong. The semantics for output parameters in T-SQL is copy-in/copy-out. But because an exception is raised, the copy-out never happens.

I have a little more discussion about this here: https://www.sommarskog.se/error_handling/Part2.html#TRY-CATCH

Aleksei Galeev 40

That's very interesting! After reading your "little discussion", I realized that there are so many things that affect the result of getting back the output value.

First of all it is TRY/CATCH in the caller, second it is THROW in my inner SP. If I write my code without them, then I can get the value from the USP_SAMPLE_PROCEDURE. Here is an example:

IF OBJECT_ID('dbo.USP_SAMPLE_PROCEDURE', 'P') IS NOT NULL
   DROP PROCEDURE dbo.USP_SAMPLE_PROCEDURE;
GO

CREATE PROCEDURE USP_SAMPLE_PROCEDURE
( 
	@Param1 int OUTPUT 
)
AS
BEGIN
	SET XACT_ABORT, NOCOUNT ON;
	
	BEGIN TRY
		SET @Param1 = 123;
		;THROW 50001, 'Test error', 0;
	END TRY
    BEGIN CATCH
		SET @Param1 = 5;
		-- Echo error information to the caller.
		--;THROW;
		DECLARE @ErrorMessage	nvarchar(2047) = ERROR_MESSAGE(),
				@ErrorSeverity	int = ERROR_SEVERITY(),
				@ErrorState		int = ERROR_STATE();
		RAISERROR  (@ErrorMessage, @ErrorSeverity, @ErrorState); --re-raise the message.
    END CATCH
END
GO

As soon as I call my new SP without TRY/CATCH, I can get 5:

DECLARE @Param1 int;
EXEC dbo.USP_SAMPLE_PROCEDURE @Param1=@Param1 OUTPUT;
PRINT @Param1;

The result is

Msg 50000, Level 16, State 0, Procedure USP_SAMPLE_PROCEDURE, Line 32 [Batch Start Line 30]

Test error

5

However, to get it working I have to deal with RAISERROR instead of THROW, which is not recommended way by Microsoft. (New applications should use THROW instead of RAISERROR.) In addition, I loose the error number (50001 in my case) because when msg_id is not specified, RAISERROR raises an error message with an error number of 50000.

So, I am thinking about other workaround. What about this one? Is there a better solution?

IF OBJECT_ID('dbo.USP_SAMPLE_PROCEDURE', 'P') IS NOT NULL
   DROP PROCEDURE dbo.USP_SAMPLE_PROCEDURE;
GO

CREATE PROCEDURE USP_SAMPLE_PROCEDURE
( 
	@Param1 int OUTPUT 
)
AS
BEGIN
	SET XACT_ABORT, NOCOUNT ON;
	
	BEGIN TRY

		SET @Param1 = 123;

		;THROW 50001, 'Test error', 0;
	END TRY
    BEGIN CATCH
		SET @Param1 = 5;
		EXEC sys.sp_set_session_context 'Param1', @Param1; 

		-- Echo error information to the caller.
		;THROW;
    END CATCH
END
GO

DECLARE @Param1 int;

BEGIN TRY
	EXEC dbo.USP_SAMPLE_PROCEDURE @Param1=@Param1 OUTPUT;
	SELECT @Param1 AS [FROM TRY]
END TRY
BEGIN CATCH
	--SELECT @Param1 AS [FROM CATCH]
	SELECT SESSION_CONTEXT(N'Param1') AS [FROM CATCH]
END CATCH
GO

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-07-04T16:03:08.8933333+00:00

Using session_context is a quite clever solution! I will need to update my article with this little trick, of course with credit to you! (In fact, I may simply drop a reference to this thread.)

As for RAISERROR, you can include the error number in the string. In Part1 of this series, I present a simple procedure error_handler_sp that embeds this operation. And, yeah, Microsoft say they recommend THROW over RAISERROR. Unfortunately, THROW comes with its own set of problem, including adding one more command for the same tihng.

Answer 2

Bruce (SqlWork.com) 77,686 Volunteer Moderator

The sp is returning an error rather than a result set. Output parameters are returned in the result set after the row data. Normally the exec would copy the output parameter result to the output parameter, but the error result bypasses this step.

Share via

How to get output value from a stored procedure in case of error

1 additional answer

Your answer