Looks can be deceiving - 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

Transact-SQL (2000)

Looks can be deceiving

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-04-25 : 05:09:15

I've had a look in BOL and I think I have this right but am sure that I read something contrary somewhere so would appreciate constructive comments.

I have two sprocs that are passed data by a main one. If I put the calls from the main one into a transaction would a rollback 'remove' any data entered in the two sub ones?

e.g. (roughly)

CREATE PROCEDURE MySub1 @Data1 int ..., @ERRORCODE1 INT OUTPUT
AS
INSERT @Data1 into MyTable1...
<Do some other stuff>

CREATE PROCEDURE MySub2 @Data2 int ..., @ERRORCODE1 INT OUTPUT
AS
INSERT @Data2 into MyTable2
<Do some other stuff too>

CREATE PROCEDURE MAIN
AS
BEGIN TRANSACTION
EXEC MySub1 1,... @ERRORCODE OUTPUT
EXEC MySub2 2,... @ERRORCODE OUTPUT
IF @ERRORCODE1 + @ERRORCODE2 = 0 -- i.e. no error code returned
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION

So if I execute my MAIN sproc and MySub2 returns an error (in @ERRORCODE2) will the insert in MySub1 rollback to before MySub1 was called?

Thanks

steve

A sarcasm detector, what a great idea.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-25 : 05:18:34

yes.


use northwind
go
create proc sub1
as
insert into orders (customerid, employeeid, shipvia)
select 'vinet', 5, 1 -- this is ok
go

create proc sub2
as
insert into orders (customerid, employeeid, shipvia)
select 'vinet', 5, 22 -- this isn't
go
create proc main
as
set xact_abort on
begin tran
exec sub1
exec sub2
commit
set xact_abort off
go

exec main
go
select 	* 
from 	orders
order by orderid desc

--delete from orders where orderid >= 11082
go 
drop proc sub1
drop proc sub2
drop proc main

Go with the flow & have fun! Else fight the flow

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-04-25 : 08:05:10

cheers, thanks spirit

steve

A sarcasm detector, what a great idea.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources