When it comes to error handling or troubleshooting a long stored procedure, RAISERROR is an easy statement to use that gets the job done. Way back in SQL Server 2012, Microsoft wanted to replace RAISERROR with the new (arguably less convenient) THROW statement. I thought it would be worth looking at an example using THROW and what it takes to have custom messaging with a parameter.
Let’s start with a basic TRY/CATCH that will cause an error based on dividing by zero:
DECLARE @Num AS INT = 100; BEGIN TRY SELECT @Num/0; END TRY BEGIN CATCH PRINT 'Error in TRY'; END CATCH;
We see that we make it to the catch. If we add THROW, we’ll get more details on why our TRY did not succeed:
DECLARE @Num AS INT = 100; BEGIN TRY SELECT @Num/0; END TRY BEGIN CATCH PRINT 'Error in TRY'; THROW; END CATCH;
All in the Details
Let’s say we want to know what the value of @Num is that is being used to divide by zero. If we are using RAISERROR, we might have this type of message:
DECLARE @Num AS INT = 100; RAISERROR('Sorry, but you cannot divide %d by zero.',16,1,@Num);
Let’s look at the same type of message but using THROW. Unlike THROW does not work with substitution parameters the same way as RAISERROR. That means we’ll want to add a custom message to sys.messages and use the FORMATMESSAGE function. To add the message we’ll run:
EXEC sys.sp_addmessage @msgnum = 50001, @severity = 16, @msgtext = N'Sorry, but you cannot divide %d by zero.', @lang = 'us_english'; GO
Once added, we can check the info on our custom message:
SELECT * FROM sys.messages WHERE message_id = 50001; GO
Now, let’s test out our THROW by passing the message and using FORMATMESSAGE:
DECLARE @Num AS INT = 100; DECLARE @Msg NVARCHAR(2048) = FORMATMESSAGE(50001, @Num); THROW 50001, @Msg, 1;
If we decide we no longer want that message sitting in sys.messages we can drop it by running:
EXEC sp_dropmessage 50001;
Throw it Away?
THROW is recommended over RAISERROR for new applications. With that said, RAISERROR doesn’t seem to be going anywhere anytime soon.
Thanks for reading!