March 25, 2010 at 10:39 am
Hi,
I have this stored procedure:
create stored procedure test
as
Begin
update table xtu set cod ='A'
End
if i execute this stored procedure from inside other stored procedure, like this:
create procedure test2
as
BEGIN
declare @erro as int
begin transaction
exec test
set @erro =@@error
if @erro =0
begin
commit transaction
end
else
rollback transaction
END
If i get an error when executing the procedure test can i rollback the transaction that was made by the procedure test? or should i place a BEGIN TRANSACTION and COMMIT TRANSACTION inside the stored procedure test , so that i can rollback transaction from within procedure test2 if procedure test fails?
Thank you
March 25, 2010 at 10:43 am
Look up Try Catch in BOL. That will help you with being able to rollback the transaction.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 26, 2010 at 3:24 pm
Check this, it should answer your question
http://msdn.microsoft.com/en-us/library/ms189336(SQL.90).aspx
March 26, 2010 at 11:29 pm
GTR (3/26/2010)
Check this, it should answer your questionhttp://msdn.microsoft.com/en-us/library/ms189336(SQL.90).aspx
This method would allow the OP to place a transaction in both stored procedures (thus nest the transactions).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply