In my previous post I wrote about the new
THROW keyword in Denali / SQL 11. Having played around a bit more with Denali, I wanted to write some additional things about
THROW and it’s relation to
First some background / overview of RAISERROR:
- RAISERROR allows you to throw an error based on either an error number or a message, and you can define the severity level and state of that error:
RAISERROR(50001, 16, 1); --or RAISERROR('Ooops', 16, 1);
- If you call
RAISERRORwith an error number, that error number has to exist in sys.messages.
- You can use error numbers between 13001 and 2147483647 (it cannot be 50000) with
As I mentioned in my previous post,
RAISERROR has been around since forever – and it works fairly well. One of the major drawbacks with
RAISERROR – as I also wrote in my previous post – is that it cannot be used to re-throw an error we might have trapped in a structured error handling block. Or rather, this may not be that much a
RAISERROR issue, as an issue that SQL Server has not previously supported the notion of re-throwing an error. Be as it may with that, there are other drawbacks with
RAISERROR which I will mention later in this post.
In Denali / SQL 11 Microsoft introduces the
THROW keyword, which allows us to re-throw an exception caught in an exception handling block. Some characteristics of
THROWyou can throw a specific error number as well as message:
THROW 50000, 'Ooops', 1;
- When using
THROW>you have to define both an error number as well as a message (and state), unless you re-throw an exception.
- The error number does not have to exist in sys.messages but, it has to be between 50000 and 2147483647.
THROW looks fairly cool, but what are the drawbacks with
RAISERROR I mentioned above? Well, for one – beginning with Denali / SQL 11
RAISERROR is being deprecated, i.e. it will eventually be removed from SQL Server. Another reason has to do with transactions and error handling.
As every T-SQL programmer worth his (or her) salt should know, an exception does not roll back a transaction by default (ok, ok, it does depend on severity level to an extent – but a “normal” exception does not roll back a tran). I.e. the following code would cause two rows to be inserted in the table
--first create a test table which we will use throughout the code samples CREATE TABLE t1 (id int primary key, col1 nvarchar(15)); --now onto the 'meat' BEGIN TRAN INSERT INTO t1 VALUES(1, 'row1'); --emulate some error, this will indeed cause an exception to happen, --but the processing will continue SELECT 1 / 0 INSERT INTO t1 VALUES(2, 'row2') COMMIT
We can indicate to SQL Server that we want “automatic” rollback of transactions when an exception happens by setting
XACT_ABORT. This will cause a rollback to happen if a system exception happens. So based on the example above, no rows will be inserted when the code below executes:
SET XACT_ABORT ON BEGIN TRAN INSERT INTO t1 VALUES(3, 'row3'); SELECT 1 / 0 INSERT INTO T1 VALUES(4, 'row4') COMMIT
However, what happens if the user throws an exception using
RAISERROR? In that case no rollback happens, i.e.
RAISERROR does not honor the
SET XACT_ABORT ON BEGIN TRAN INSERT INTO t1 VALUES(5, 'row5'); --the user raises an error, but the tx will not roll back RAISERROR('Oooops', 16, 1) INSERT INTO t1 VALUES(6, 'row6') COMMIT
This can catch developers out and is in my opinion a fairly severe drawback. So with the introduction of Denali / SQL 11 and the
THROW keyword, Microsoft has tried to fix this by making
SET XACT_ABORT ON BEGIN TRAN INSERT INTO t1 VALUES(7, 'row7'); --the user raises an error, and the tx will roll back THROW 50000, 'Ooops', 1 INSERT INTO t1 VALUES(8, 'row8') COMMIT
When you run the code above, you will see that the transaction is indeed rolled back and no rows are inserted.
So developers, “go forth” and
THROW exceptions in SQL Server Denali / SQL 11.
You can subscribe to my RSS feed at: http://feeds.feedburner.com/manageddata.
I am also at twitter as @nielsberglund