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
+2
A:
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.
TLiebe
2009-10-22 16:36:20
I work with SQL-SERVER 2008
Bitnius
2009-10-22 16:49:08
Take a look at http://www.4guysfromrolla.com/webtech/080305-1.shtml. There sample calls several SQL statements but you could just as easily call other stored procedures.
TLiebe
2009-10-22 16:52:18
+1
A:
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
Gratzy
2009-10-22 16:39:10
If the transaction can be "contained" entirely in one database server instance, for simplicity's sake I'd implement it *within* that database call. I'd only implement it outside the database if it absolutely had to cover work done outside the database.
Philip Kelley
2009-10-22 16:43:45
I don't disagree with you some people however prefer to use an ORM framework or some similar and prefer to have as little database objects as possible
Gratzy
2009-10-22 16:45:33
+3
A:
begin transaction
begin try
exec proc_1
exec proc_2
exec proc_3
commit transaction
end try
begin catch
rollback transaction
end catch
Ray
2009-10-22 16:39:11
You'll want to expand on the error handling shown here, and then test it until you're sick of it. Messed up "transaction control" can produce truly hideous results.
Philip Kelley
2009-10-22 16:41:29
indeed - my simple example will not report errors back to the calling application
Ray
2009-10-22 16:42:58