In this post let me take a shot at showing some of the nice features SQL Server THROW command. When I planned to write this post, was tempted to write about Error handling, TRY-CATCH, Raiserror commands etc. But I have had the unique privilege of reading the Joes2pros Series and the basics are explained well there. Interestingly, Pinal Dave did run few contests around Joes2Pro’s series sometime back and I thought it will be better to just link the concepts rather than rehashing the efforts already done. Read about Error Messages and RAISERROR, Structured Error Handling with TRY-CATCH explained. They are a great start to learn those basics.
Let me take a moment to start the simplest form of throw. Assume we get an error inside a nested procedure and we want to bubble it up. the previous avatar was to use the RAISERROR and it was quite an task sometimes. In the THROW construct it can be simply done with a single THROW command. like below.
THROW 51051, ‘I come from the THROW construct :)’, 1
The output for the above batch will be:
There is a small thing to take care, the previous statement before the THROW construct needs to have a semicolon – else an error is raised. This is one of the requirement for THROW. A typical error would be like below:
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ‘THROW’.
This is one of the common mistakes all of us make and hence thought will be worth sharing here.
With Custom Message
The second common mechanism to use RAISERROR with applications is to add an custom message and then invoke the same parameterized via the RAISERROR. Let me show a fundamental functionality of creating a custom error message and then raising the same.
EXECUTE sp_addmessage @msgnum=55055, @severity=16,
@msgtext=N’This error was raised for %s user’,
RAISERROR (55055, 16,1, ‘vinod’)
The output for the above batch would be as below. You can see the username has been properly replaced in the final message as it got rendered.
To get the same effect with the THROW statement we need to manually format the message before using the same. A typical construct would be:
DECLARE @message NVARCHAR(2048) = FORMATMESSAGE(55055, ‘vinod’);
THROW 55055, @message, 1;
This gives the same output as above and behaves the same way as before as we used with RAISERROR.
As you can see, we have used the FORMATMESSAGE function to make the replacement with RAISERROR as it allows the error message to be formatted to be used later with the THROW statement.
Hope this new construct of THROW will be useful and I highly recommend we use this new feature of SQL Server 2012 for our future coding practices. Do tell me if you have used this in your environments already. Will be interested in listening to your usecases.
This entry was posted on Thursday, October 11th, 2012 at 19:10 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.