SQL Server – THROW and Output variables: Output variables are not returned when an error occurs

With SQL Server 2012, the THROW statement was introduced to T-SQL.  On the whole, I find the syntax and usability of THROW to be more intuitive than RAISERROR, however I came across an interesting difference today: the way in which they handle output variables from a stored procedure.

This discovery happened whilst working with a procedure that identified some invalid data during some process, wrote those rows out to an error table, and raised an alert with the number of rows that were set aside.  Bizarrely, after a code change which included changing the RAISERROR to the more friendly THROW statement, the alert started only showing 0 rows added to the errors table despite raising an alert that something went wrong.  On investigation into the issue, several rows were being written away and so I was quite surprised where the issue was coming from.  It turned out that the value for the number of rows was passed to an output variable and then handled, and this value was not getting populated; after a little investigation, it would appear that the THROW statement was the culprit, as I’ll now show.

Below is a sample procedure using the old style RAISERROR in combination with an output variable, in this case the procedure is exceptionally simple, and simply sets the variable to the value 10 before using RAISERROR to create our error message.

CREATE PROCEDURE dbo.uspTestRaiserror
@Output INT OUTPUT
AS
BEGIN
	/* set the output variable */
	SELECT @Output = 10;

	/* raise an error*/
	RAISERROR('Some error in dbo.uspTestRaiserror', 16,1);
END
GO

/*declare a variable to hold our output variable*/
DECLARE @Output INT;

/* execute the procedure passing the output variable out*/
EXEC dbo.uspTestRaiserror @Output = @Output OUTPUT;

/* select the output variable*/
SELECT @Output;
GO

When running the code, we get the following results; an error and the return of the output parameter set to 10:-

If we create another procedure using the THROW statement instead of RAISERROR, it will look something like this:-

CREATE PROCEDURE dbo.uspTestThrow
@Output INT OUTPUT
AS
BEGIN
	/* set the output variable*/
	SELECT @Output = 12;

	/* throw an error*/
	THROW 50000, 'Some error dbo.uspTestThrow', 2;
END
GO

/*declare a variable to hold our output variable*/
DECLARE @Output INT;

/* execute the procedure passing the output variable out*/
EXEC dbo.uspTestThrow @Output = @Output OUTPUT;

/* select the output variable*/
SELECT @Output;
GO

This time when we run the procedure, all we get back is the following:-

Throw .png

This difference in behavior doesn’t appear to be documented on the THROW page on Books Online, so thought it was worth highlighting; will hopefully save someone some time troubleshooting in the future!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s