Have RAISERROR work with XACT_ABORT - by jorundur

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 275308 Comments
Status Closed Workarounds
Type Suggestion Repros 1
Opened 5/2/2007 11:10:06 AM
Access Restriction Public


The following code...

CREATE TABLE mytable (mycolumn int NOT NULL)
  RAISERROR ('My Error', 16, 1)  
  INSERT INTO mytable (mycolumn) VALUES (NULL)
  INSERT INTO mytable (mycolumn) VALUES (NULL)
EXEC myproc

...gives the following 2 errors...

Msg 50000, Level 16, State 1, Procedure myproc, Line 4
My Error
Msg 515, Level 16, State 2, Procedure myproc, Line 5
Cannot insert the value NULL into column 'mycolumn', table 'ZCORE.dbo.mytable'; column does not allow nulls. INSERT fails.

...meaning it continues after the first RAISERROR but stops after the first INSERT which fails when trying to insert NULL into a NOT NULL column.  I dont get this.  I would like to be able to use XACT_ABORT ON in proc A which calls proc B which raises an error and then proc A will abort the transaction.  Now I will have to use TRY/CATCH for every nested proc call.  TRY/CATCH is great by the way, thank you very much for that.
Sign in to post a comment.
Posted by Microsoft on 2/1/2008 at 11:16 AM

Thank you for your feedback. By design, the XACT_ABORT set option does not impact the behavior of the RAISERROR statement. We will consider your feedback to modify this behavior for a future release of SQL Server.

-- SQL Server Engine Team
Posted by Grunthos on 10/8/2007 at 8:43 PM
At leat make this behaviour optional via a database setting in order to preserve backward compatibility.

In any case I see no reason to have RaisError behave differently when inside a TRY/CATCH block. This is very odd.