Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2009
    Posts
    168

    Unanswered: Stored Procedure Transaction Commit Problem

    Hi,

    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.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why are you performing 3 separate transactions then? Can all the work be wrapped up into a single sproc?
    Code:
    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 (
       id int
    )
    
    BEGIN TRAN
    
    INSERT INTO @a
      OUTPUT inserted.a_id INTO @newly_inserted_rows (id)
      DEFAULT VALUES
    
    INSERT INTO @b (a_id)
      SELECT id
      FROM   @newly_inserted_rows
    
    COMMIT TRAN
    
    SELECT a_id
    FROM   @a
    
    SELECT a_id
         , b_id
    FROM   @b
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2009
    Posts
    168
    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:

    Sproc1
    ------
    begin tran
    commands...
    ...etc

    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)
    if yes
    commit
    else
    rollback

    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?

  4. #4
    Join Date
    Jul 2009
    Posts
    168
    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...

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    You can pass a parameter between SPs by defining an OUTPUT parameter in the SPs.
    Code:
    CREATE PROCEDURE dbo.P_doSomething (
    	@SomethingIn BIGINT,	-- Input 
    	@SomethingOut CHAR(1) OUTPUT	-- Output 
    	)
    AS
    BEGIN
    ...
    	SET SomethingOut = 'Y'
    ...
    END 
    
    @ReturnValueFromP_doSomething	CHAR(1)
    
    EXECUTE dbo.P_doSomething @SomethingIn = 12345789,
    			@SomethingOut = @ReturnValueFromP_doSomething OUTPUT
    
    EXECUTE dbo.P_doSomethingElse @ReturnValueFromP_doSomething
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Consider changing your sprocs in to functions that return a dataset.
    George
    Home | Blog

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    don't you have all the values in the application layer?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jul 2009
    Posts
    168
    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.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    For functions, I always refer to this article first : User Defined Functions - SQLTeam.com


    Pay attention to the Multi-Statement UDFs
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •