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:-
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!