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