casinos

SQL Server 2012: THROW your errors October 11th, 2012

Vinod Kumar

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.

From SQL Server 2012, it is recommended to use the THROW syntax instead of the conventional RAISERROR syntaxes. The MSDN documentation for THROW syntax can be got here.

Simple usage

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.

BEGIN TRY
    THROW 51051, ‘I come from the THROW construct :)’, 1
END TRY
BEGIN CATCH
    PRINT ERROR_NUMBER();
    THROW;
END CATCH

The output for the above batch will be:

image

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’,
@replace=’replace’
GO
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.

image

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.

Final words

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.

Tags: , , , , , , ,

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.


5 Responses to “SQL Server 2012: THROW your errors”

  1. Aasif Risad says:

    Sir
    I am stuck in THROW statement.
    I am using SQL Server 2012, previously I created the sp on SQL Server 2005 and used RAISERROR.
    Now it has to be migrated in SQL Server 2012 & I have applied THROW in place of raiserror but when I use throw then I am not able to get the value of OUTPUT PARAMETER in my code.
    It gets only nothing with OUT PARAMETER before it is working with RAISERROR and I am able to access the OUT PARAMETER value.

    Please help me as I am stuch in this.
    How can I get the output param value in code if I am using the throw keyword in sql server 2012 ?????

    Regards,
    Aasif Risad

  2. Govind says:

    Vinod-Usage of throw in server side should be matched up on client side to ensure right “error message” is bubbled up and logged/captured.

Leave a Reply