THROW instead of RAISERROR
Published: Feb 12, 2016
I’ve done a couple of posts about RAISERROR, but if you’re using SQL Server 2012 and above then there’s a new syntax also available which I think is a little better. That syntax is THROW.

This does a very similar job to RAISERROR but has a couple of advantages that I want to highlight.

It should also be noted that the syntax of THROW is also a little cleaner and less cumbersome than RAISERROR and therefore this is what I tend to use, version permitting.

So how do we use THROW in comparison to RAISERROR. Well let’s look at an example…

This is our RAISERROR syntax:

declare @errMess nvarchar(1000),
          
@errSev smallint,
          
@errState smallint

begin try
  
select 1 / 0
end try
begin catch
  
select @errMess = ERROR_MESSAGE(),
          
@errSev = ERROR_SEVERITY(),
          
@errState = ERROR_STATE()
  
raiserror(@errMess, @errSev, @errState)
end catch


Which gives us the following output:

Now let’s use THROW:

begin try
  
select 1 / 0
end try
begin catch
   throw
end catch


Which gives us the following output:

Now the first thing to note is that the THROW syntax is MUCH cleaner and far simpler to work with. There are no variables required, and therefore a much smaller piece of code which is far easier to read.

The other point to note is that RAISERROR will output the Error Line as the line of code in which RAISERROR was called. In this case that would be Line 12. Not very useful. However THROW will give you the actual line number of the offending t-SQL. In this case Line 2.

For these reasons alone I much prefer THROW when coding in SQL Server 2012 or above.

The one thing of note though, is that if you wish to raise a lower severity error then you cannot do this with THROW as it will always use a level 16 error.

However, if that doesn’t bother you, then I’d go with THROW all the way.

Another thing of note is that you can parameterize THROW by adding your own messages, for example:

begin try
  
select 1 / 0
end try
begin catch
   throw 50001
, 'This is an error', 1
end catch


However, doing this simply reverts THROW back to RAISERROR behaviour and you’ll be back to having the incorrect error line reported:

But as this is simply RAISERROR behaviour I would still always use THROW unless I want to specify an error level.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron