Unanswered: Stored Procedure Transaction Commit Problem
I have 3 stored procedures each executing insert statements in the db. I am using ASP.NET to pass parameters from and to the stored procedures by handling their data sources' events.
- First procedure inserts a record in a table returning the newly inserted row
- Second procedure gets this value and and uses it for insertion to other tables
The problem is all the insert statements in those 2 procedures need to succeed before being committed to the database. If one fails, a rollback must be done. However, the statements are in 2 separate procs. Is there a way to achieve this? Thanks.
Why are you performing 3 separate transactions then? Can all the work be wrapped up into a single sproc?
DECLARE @a table (
a_id int identity(9,37)
DECLARE @b table (
b_id int identity(93,7)
, a_id int
DECLARE @newly_inserted_rows table (
INSERT INTO @a
OUTPUT inserted.a_id INTO @newly_inserted_rows (id)
INSERT INTO @b (a_id)
Thanks for the reply. I want to use different sprocs because there are too many sql commands and all bundled in one will make it difficult to follow and debud hence the idea of dividing it into 3 parts. I did practically the same thing to pass on values between them by creating temporary tables and then going like this:
exec sproc2 (create temporary table there to hold values i want to pass on)
exec sproc3 (create temporary table there to hold values i want to pass on)
check if the returning values from temporary tables have passed (true/false)
I havent tested it yet but that's how I wanted to carry it out and i guess that's what you basically did in a different way by creating those temporary tables?
nops the thing doesnt work. I havent been able to really find a solution to this problem anywhere. Is that so difficult to make a procedure store something and another retrieve a value using stored procedures??? very strange because it's just a matter of storing in memory and retrieving like most programming languages do but it seems a big thing using sprocs. Is that so but i get this impression that it is.
I didnt want to use a single proc because it can get messy but till I have another solution I think i will just get on like this...
Thanks for your replies guys. I was a bit confused there. I find the idea of using functions nice, any example? Continuing this discussion, I now have a similar problem where I need to pass errors out of the sproc to my application layer. Like say, I tried to enter data to a table but due to duplication the data is not committed, so I want to return an error message out of this sproc. If I have a number of error messages how do deal with this?
I was considering using functions to say like store everything in a table and then returning this to my application. I am not sure how to achieve this at the moment.