CODEWITHSUNDEEP
sql-serversql-server-2008stored-procedurestransactions
RicardoBalda
RicardoBalda

Reputation: 1747

How to make a trasanction cover several stored procedure?

I need to include several stored procedure in a single transaction in a single database, if any of stored procedure fail then roll back transaction of all stored procedure procesed in the scope.

I work with SQL-SERVER 2008

Upvotes: 4

Views: 4401

Answers (4)

Gratzy
Gratzy

Reputation: 9389

Transactions are usually at the connection level, so if you want to control the transaction through a code api you should be able to use the same "transaction object".

.Net example http://msdn.microsoft.com/en-us/library/2k2hy99x.aspx using ado.net

Upvotes: 1

jpg
jpg

Reputation: 455

Cant describe the solution any better than this.

Handling SQL Server Errors in Nested Procedures

Upvotes: 1

Ray
Ray

Reputation: 21905

begin transaction
begin try
  exec proc_1
  exec proc_2
  exec proc_3
  commit transaction
end try
begin catch
  rollback transaction
end catch

Upvotes: 5

TLiebe
TLiebe

Reputation: 7996

You can create a single stored procedure that starts a transaction and then calls the other stored procedures. If any of the inner stored procedures fail you can rollback the transaction. If you tell us what database platform you're using (MS SQL Server, MySQL, etc.) people may be able to provide more specific solutions.

Upvotes: 2

Related Questions