SQLTeam.com | Weblogs | Forums

Catch SQL Error Level 16 in Child SProc which uses XACT_ABORT ON?


#1

Can I detect an error in a Child Sproc which aborts with an error (Level 16 / Transaction Doomed etc.) if that Child Sproc uses SET XACT_ABORT ON?

Test Rig

CREATE PROCEDURE #TEMP_Child
AS
-- OFF works OK, ON terminates the batch and control does not return to Caller
SET XACT_ABORT ON

-- SELECT * FROM NonExistentTable		-- Alternative test
INSERT INTO #TEMP_Table(MyColumn)
SELECT	NULL	-- Table does not allow NULLs
GO

CREATE PROCEDURE #TEMP_Parent
AS
SET XACT_ABORT OFF
CREATE TABLE #TEMP_Table
(
	MyColumn	int NOT NULL
)
RAISERROR (N'Exec #TEMP_Child START', 10, 1) WITH NOWAIT
EXEC #TEMP_Child
RAISERROR (N'Exec #TEMP_Child END', 10, 1) WITH NOWAIT
GO

RAISERROR (N'Exec #TEMP_Parent START', 10, 1) WITH NOWAIT
EXEC #TEMP_Parent
RAISERROR (N'Exec #TEMP_Parent END', 10, 1) WITH NOWAIT
GO
DROP PROCEDURE #TEMP_Parent
GO
DROP PROCEDURE #TEMP_Child
GO

Using SET XACT_ABORT OFF:

Exec #TEMP_Parent START
Exec #TEMP_Child START
Server: Msg 515, Level 16, State 2, Procedure #TEMP_Child, Line 5
Cannot insert the value NULL into column 'MyColumn', table 'tempdb.dbo.#TEMP_Table'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Exec #TEMP_Child END
Exec #TEMP_Parent END

Using SET XACT_ABORT ON:

Exec #TEMP_Parent START
Exec #TEMP_Child START
Server: Msg 515, Level 16, State 2, Procedure #TEMP_Child, Line 5
Cannot insert the value NULL into column 'MyColumn', table 'tempdb.dbo.#TEMP_Table'; column does not allow nulls. INSERT fails.