THROW Statement Usage

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.

Try Me

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!

One thought on “THROW Statement Usage”

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s