Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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

Categories


© Copyright 2020 SQLTraining Ltd.