Home Dashboard Directory Help

Have RAISERROR work with XACT_ABORT by jorundur


Status: 

Closed
 as Won't Fix Help for as Won't Fix


19
0
Sign in
to vote
Type: Suggestion
ID: 275308
Opened: 5/2/2007 11:10:06 AM
Access Restriction: Public
0
Workaround(s)
view

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.
Details
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.
Sign in to post a workaround.