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.


19
0
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

Description

The following code...

CREATE TABLE mytable (mycolumn int NOT NULL)
GO
CREATE PROC myproc
AS
  SET XACT_ABORT ON
  RAISERROR ('My Error', 16, 1)  
  INSERT INTO mytable (mycolumn) VALUES (NULL)
  INSERT INTO mytable (mycolumn) VALUES (NULL)
GO
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
Hello

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.