Rollback stored procedure transactions 1

[Jusenkyo]

Hello all

I have 2 stored procedures that run a bunch of updates/appends/deletes. The first SP runs a few updates (each with rollback transactions), then calls the second procedure.

I need to rollback whatever this second SP does if it fails, and rollback all transactions in the first SP.

The code goes a little like this:
ALTER PROCEDURE "spAppendPostPrintData" AS
Begin Transaction

INSERT INTO dbo.tblPrintRecord (HubeAccNo, SERVICE_NO)
SELECT HubeAccNo, SERVICE_NO
FROM dbo.tblSalesOppsPrint
IF @@ERROR <>0
BEGIN
ROLLBACK TRAN
RETURN
END

EXEC spAppendPostPrintData2
COMMIT TRANSACTION

I want to rollback the EXEC command, and all that goes before it.

Hope that makes sense!
Cheers
J

 

[JamesLean]

1) Don't do BEGIN TRANSACTION in the second SP.

2) Because you've explicitly begun a transaction in the first SP, and you're still inside it when you call the second SP, if you issue a ROLLBACK in the second SP it should automatically rollback the entire transaction.

--James

 

[Jusenkyo]

Excellent! That easy aye!

Cheers james

 

[Jusenkyo]

Excellent! Thanks James.

Do I put a COMMIT TRANSACTION in the second stored procedure then?

 

[JamesLean]

Nope. The commit is taken care of in the first SP.

--James

 

[hmckillop]

alternatively what you can do if you are not sure if your procedures plan to be called by other procedures but you still need to manage transactions is you can check first i.e.

DECLARE @v_InTransaction 		INT
--call at start of procedure
SET @v_InTransaction=@@TRANCOUNT
IF @v_InTransaction=0
BEGIN
	BEGIN TRANSACTION
END   
/*...do my stored proc code in here */

IF @v_InTransaction=0
BEGIN
	COMMIT TRANSACTION
END

Assuming then if you need to do a rollback, it will rollback the outer transaction also. If you dont want this to happen, then why have this proc in the transaction!
Cheers


"I'm living so far beyond my income that we may almost be said to be living apart