February 2, 2011 at 2:32 am
hi
i have 2 tables( tb1 and tb2) and write two sp ( sp1 , sp2) :
create procedure sp1
/* some parameters */
as
begin
begin try
begin transaction
insert tb1 values /* some values*/
commit transaction
end try
begin catch
rollback transaction
end catch
end
create procedure sp2
/* some parameters */
as
begin
begin try
begin transaction
insert tb2 values /* some values*/
commit transaction
end try
begin catch
rollback transaction
end catch
end
how can I check two procedures run successfully?
I want ,If the second sp roll back, roll back to the first sp
February 2, 2011 at 4:42 am
If I'm understanding the requirements correctly, I'd put the second procedure into a transaction with a TRY/CATCH block that will allow you to roll it back if there are errors. Please check this article[/url] for ideas how to do that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 2, 2011 at 7:36 am
hi dear
(I can not write English well)
I use these procedures in my application.But I want if in the second stored procedure transaction is rolled back , the first transaction will roll back .
February 2, 2011 at 7:59 am
If you nest the transactions, start one that wraps both procedures and start a second that only wraps the second procedure, you can rollback the second transaction without rolling back the first. I'd name them too. It'll make it easier to management.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 2, 2011 at 8:45 am
Hi Dear
Please Explain more
February 2, 2011 at 8:54 am
Something like this:
BEGIN TRANSACTION MyTran1
EXEC dbo.MyProcA
BEGIN TRY
BEGIN TRANSACTION MyTran2
EXEC dbo.MyProcB
COMMIT TRANSACTION MyTran2
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION MyTran2
END CATCH
COMMIT TRANSACTION MyTran1
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply