Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: How to rollback only single stored procedure action?

    Hi,
    I would like to create a stored procedure which runs two stored procedures one after another. If first stored procedure runs successful and second fails than I do NOT want to rollback first stored procedure SQLs.

    I have written bellow simple sample. But if I execute admin.sp_run and force application in the time second stored procedure is executing (admin.sp2) then SQL from first stored procedure (admin.sp1) is also rollbacked.

    Is there any way I can write a admin.sp_run procedure in such a way that if admin.sp2 procedure fails it does not rollback admin.sp1 actions.

    Code:
    CREATE TABLE ADMIN.TAB (COL1 INT)@
    
    CREATE PROCEDURE ADMIN.SP1
      LANGUAGE SQL
      BEGIN
        INSERT INTO ADMIN.TAB VALUES (1);
      END@
    
    CREATE PROCEDURE ADMIN.SP2
      LANGUAGE SQL
      BEGIN
        UPDATE ADMIN.TAB SET COL1 = 2;
      END@
    
    CREATE PROCEDURE ADMIN.SP_RUN
      LANGUAGE SQL
      BEGIN
         CALL ADMIN.SP1 ();
         CALL ADMIN.SP2 ();
      END
    @
    
    CALL ADMIN.SP_RUN()
    Thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    I have found out. Commit statement must be inserted into first procedure like:
    Code:
    CREATE PROCEDURE ADMIN.SP1
      LANGUAGE SQL
      BEGIN
        INSERT INTO ADMIN.TAB VALUES (1);
        COMMIT;
      END@

Posting Permissions

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