Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2009
    Posts
    3

    Unanswered: Rolling back commited nested stored procedure changes..

    I have a requirement where I have one main stored procedure that calls several nested stored procedures - that in turn make some table updates - and when one of those stored procedures fails the changes that all the previous stored procedures had made need to be rolled back. The bind is that only the main stored procedure can be changed and the nested ones can not. If the nested stored procedures run successfully they do commit their changes. How can I program into the main stored procedure to: 1) Recognize when the stored procedure failed 2) Rollback all changes that the previous stored procedures have made?

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That is a pretty crappy restriction, TBH. This would of course be fairly trivial if you could alter all the procedures.

    I can't really see how you can do this safely while maintaining concurrent access for other users.
    Since you have this restriction, is it possible to totally bypass these other procedures? For example, deprecate them and put the altered code in your own procedure(s)?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That is a pretty crappy restriction, TBH. This would of course be fairly trivial if you could alter all the procedures.

    I can't really see how you can do this safely while maintaining concurrent access for other users.
    Since you have this restriction, is it possible to totally bypass these other procedures? For example, deprecate them and put the altered code in your own procedure(s)?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    dup post

    this massage is too short...
    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.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Is this what you are describing?

    Code:
    CREATE TABLE myTable99(Col1 int PRIMARY KEY, Col2 char(1), Col3 datetime)
    GO
    
    CREATE PROC Sproc1 AS 
      BEGIN TRAN
    	INSERT INTO myTable99(Col1) SELECT 1
      COMMIT TRAN
    GO
    CREATE PROC Sproc2 AS 
      BEGIN TRAN
    	INSERT INTO myTable99(Col1, Col2) SELECT 2, 'b'
      COMMIT TRAN
    GO
    CREATE PROC Sproc3 AS 
      BEGIN TRAN
    	INSERT INTO myTable99(Col1, Col2, Col3) SELECT 3, 'c', GetDate()
      COMMIT TRAN
    GO
    
    CREATE PROC Sproc4 AS 
      BEGIN TRAN
    	EXEC Sproc1
    	EXEC Sproc2
    	EXEC Sproc3
      ROLLBACK TRAN
    GO
    
    EXEC Sproc4
    GO
    
    SELECT * FROM myTable99
    GO
    
    DROP PROC Sproc1,Sproc2,Sproc3, Sproc4
    GO
    
    DROP TABLE myTable99
    GO
    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.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Don't you just hate it when you find out you didn't know something clearly pretty fundamental? I've spent the last few years ensuring all transaction management is in the "parent" procedure for this sort of thing and now it turns out it was for no reason at all
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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