Thursday, March 29, 2012

Can I have Exec(...) statements within a transcation ?

Is the following T-SQL correct, in which I am trying to make sure that th effect of 3 stored procedures gets reversed in case of an error ? I know if in place of stored procedures I had action queries like 'ActionQry1', 'ActionQry2' and 'ActionQry3' then the transaction logic would work. But will it work even if exec(...) statements are there in the transaction ? Each stored procedure is made up of an action query.

begin tran
exec("storeprocedure1('2')")
if @.@.error=0
begin
exec("storeprocedure2")
if @.@.error=0
begin
exec("storeprocedure3(122)")
if @.error=0
commit tran
else
rollback tran
end
else
rollback tran
end
else
rollback transure, exec statements will work,
but your syntax is not correct, it should be like this:


begin tran
statement1

if (@.@.error <> 0)
begin
rollback tran
return
end

statement2
if (@.@.error <> 0)
begin
rollback tran
return
end

commit tran

No comments:

Post a Comment