sproc Transaction Question - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

sproc Transaction Question

Author

Topic

joliver
Starting Member

13 Posts

Posted - 2002-08-01 : 16:07:10

If I do something like this inside a stored procedure:

-------- sproc1 --------
begin transaction

update sometable
set something = 2
where PK = 3
if @@error <> 0 begin
RAISERROR('houston, we have a problem', 16, 1)
rollback
end
exec sproc2
commit transaction

-------- sproc2 --------
begin transaction
insert into foo (bar1, bar2)
values ('bar1, bar2')
if @@error <> 0 begin
RAISERROR('houston, we have a problem', 16, 1)
rollback
end
commit transaction

My goal is to have atomicity across sproc1 and sproc2. Either everything commits, or nothing commits. sproc2 can and will be executed from other places however. Whats the best way to make this happen?

Will the transaction in the calling stored procedure 'wrap' the exec, and I should avoid having a transaction inside the second stored procedure? I thought about this, but I'm afraid of a situation where something calls sproc2 outside of a transaction, so I wanted to do it the way I've shown above, I just can't figure out if it'll work or not.

If the raiseerror in sproc2 will throw an error that is reflected in @@error within the context of sproc1, I imagine this will work, but I'm not sure.

Anyone done something like this before and have an idea how it'll play out?

joshb
Yak Posting Veteran

52 Posts

Posted - 2002-08-01 : 16:14:46

BOL has a pretty good explanation of this, look up nested transactions (transactions, nested) in the index.

joliver
Starting Member

13 Posts

Posted - 2002-08-01 : 16:54:22

Answered my question perfectly, thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources