outer sp throw proper error. inner sp throw only 6401 error(Cannot roll back OP2. No transaction or savepoint of that name was found.)

USE TEST

DROP TABLE NestedTbl
CREATE TABLE NestedTbl(slno tinyint not null primary key identity, Val VARCHAR(20) UNIQUE )

 

ALTER PROC NestedOp1
AS
BEGIN
SET XACT_ABORT, NOCOUNT ON
BEGIN TRY
BEGIN TRAN OP1
INSERT INTO NestedTbl(Val) VALUES(1)
–INSERT INTO NestedTbl(Val) VALUES(1)
EXEC NestedOp2
COMMIT TRAN OP1
END TRY
BEGIN CATCH

IF @@TRANCOUNT<>0 ROLLBACK TRAN OP1
PRINT ‘SP 1’

PRINT ERROR_MESSAGE()
PRINT ERROR_SEVERITY()
PRINT ERROR_STATE()
PRINT ERROR_NUMBER()
PRINT ERROR_LINE()
END CATCH
END
ALTER PROC NestedOp2
AS
BEGIN

BEGIN TRY
BEGIN TRAN OP2
INSERT INTO NestedTbl(Val) VALUES(1)
EXEC NestedOp3
COMMIT TRAN OP2
END TRY
BEGIN CATCH
IF @@TRANCOUNT<>0 ROLLBACK TRAN OP2
PRINT ‘SP 2’
PRINT ERROR_MESSAGE()
END CATCH
END
CREATE PROC NestedOp3
AS
BEGIN
BEGIN TRY
BEGIN TRAN OP3
INSERT INTO NestedTbl(Val) VALUES(1)
COMMIT TRAN OP3
END TRY
BEGIN CATCH
IF @@TRANCOUNT<>0 ROLLBACK TRAN OP3
PRINT ERROR_MESSAGE()
PRINT ERROR_MESSAGE()
PRINT ERROR_SEVERITY()
PRINT ERROR_STATE()
PRINT ERROR_NUMBER()
PRINT ERROR_LINE()
END CATCH
END

 

SELECT * FROM NestedTbl
EXEC NestedOp1

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s