about rollback in parent procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

about rollback in parent procedure

Author

Topic

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-09-18 : 00:17:52

i do not want to rollback child procedure when i am rollbacking parent procedure after execution of child procedure. in child procedure i am committing transaction and in parent rollbacking transaction

is it possible
is there any solution to avoid rollbacking child procedure when i rollbacking parent procedure

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-18 : 00:33:55

No as the child stored proc is participating in the transaction of the parent. The workaround is to call the stored procs separately and not nested.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-09-18 : 05:00:18

CREATE TABLE tbl_A (
Aid INT
,Aname VARCHAR(20)
)

CREATE TABLE tbl_B (
Bid INT
,Bname VARCHAR(20)
)

INSERT INTO tbl_A
VALUES (
1
,'userA'
)

INSERT INTO tbl_B
VALUES (
1
,'userB'
)

ALTER PROCEDURE usp_A1
AS
BEGIN
BEGIN TRANSACTION

BEGIN TRY
UPDATE tbl_A
SET Aname = 'updated A'
WHERE Aid = 1

EXEC usp_B1

--COMMIT TRANSACTION
ROLLBACK TRANSACTION
END TRY

BEGIN CATCH
PRINT ERROR_MESSAGE()

ROLLBACK TRANSACTION
END CATCH
END

EXEC usp_A1

SELECT *
FROM tbl_A

SELECT *
FROM tbl_B

ALTER PROCEDURE usp_B1
AS
BEGIN
BEGIN TRANSACTION

BEGIN TRY
UPDATE tbl_B
SET Bname = 'updated B'
WHERE Bid = 1

COMMIT TRANSACTION

END TRY

BEGIN CATCH
PRINT ERROR_MESSAGE()

ROLLBACK TRANSACTION
END CATCH
END

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-09-18 : 05:02:23

here in above script ...
when usp_A1 rollbacks then usp_B1 also rolback

i want.... usp_B1 does not rollback
is it possible
or there is any other solution for that
... please reply me

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-09-18 : 05:13:32

As suggested by tkizer, if you are calling one sp from another, child sp will participate in parent sp's transaction and there is no way you can prevent child sp to be rolled back if parent sp is rolled back.

The only way to prevent it is to not call it from parent sp.

Harsh Athalye
http://www.letsgeek.net/

TajKazi
Posting Yak Master

101 Posts

Posted - 2014-09-18 : 06:05:24

tx tkizer and harsh_athalye..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources