Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    176

    Unanswered: TRANSACTIONS - BEGIN,END,COMMIT and ROLLBACK

    Hi,

    I need help in embedding TRANSACTION statements (BEGIN,END,COMMIT & ROLLBACK) into the flow of T-SQL code. Is this flow of transactions correct:

    Create Procedure
    Declare variables

    Set few of the above variables to some values

    /* Begin Transaction */
    BEGIN TRAN

    Insert into table A
    select from table B

    SELECT @ERRORNUM = @@ERROR
    SELECT @LOCALROWCOUNT = @@ROWCOUNT

    IF @ERRORNUM = 0
    BEGIN
    IF @LOCALROWCOUNT >= 1
    BEGIN
    COMMIT TRAN /* Everything OK so COMMIT */
    SELECT @RETURNVALUE = 0
    END
    ELSE
    BEGIN
    ROLLBACK TRAN /* Not OK so ROLLBACK */
    SELECT @RETURNVALUE = 1
    RAISERROR ('INSERT FAILS',
    16, 1)
    END
    END
    ELSE
    BEGIN
    ROLLBACK TRAN /* Not OK so ROLLBACK */
    SELECT @ERRORMESSAGETXT = description FROM [master].[dbo].[sysmessages]
    WHERE error = @@ERROR
    RAISERROR (@ERRORMESSAGETXT, 16, 1)
    SELECT @RETURNVALUE = 1
    END

    RETURN @RETURNVALUE

    END TRAN
    /* END TRANSACTION */

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    FWIW, I would impliment your code like this....

    Code:
    Create Procedure Yadayadayada
    Declare variables
    Set few of the above variables to some values
    
    /* Begin Transaction */
    BEGIN TRAN
    
    --
    -- Insert into table A and record @@error & @@rowcount
    -- These values will change with the execution of the next statment!
    --
    Insert into table A
    select @ERRORNUM = @@error
         , @LOCALROWCOUNT = @@ROWCOUNT
    
    --
    -- Test for Problems
    --
    IF (@ERRORNUM = 0 and @LOCALROWCOUNT >= 1) BEGIN
      COMMIT TRAN                /* Everything OK so COMMIT */
    
      -- 
      -- Might as well wait to return the contents of the table
      -- till after commiting the transaction.
      --
      select from table B
      SELECT @RETURNVALUE = 0
    END ELSE BEGIN
      ROLLBACK TRAN              /* Not OK so ROLLBACK */
    
      --
      -- I doubt this is needed. When an insert fails SQL Server will generate 
      -- an error message. Still it will not hurt anything.
      --
      SELECT @ERRORMESSAGETXT = description FROM [master].[dbo].[sysmessages]
       WHERE error = @@ERROR
      RAISERROR ('INSERT FAILS: %s',16, 1,@ERRORMESSAGETXT)
      SELECT @RETURNVALUE = 1
    END
    
    RETURN @RETURNVALUE
    
    --
    -- I wasn't able to find a reference to "END TRAN".
    -- is this legit?
    --
    -- END TRAN  /* END TRANSACTION */
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: TRANSACTIONS - BEGIN,END,COMMIT and ROLLBACK

    MOO

    Try to keep to 1 BEGIN, COMMIT and ROLLBACK...unless it doesn't make sense..just makes life easier

    which in your case I think you can...only time I deviate is when I do a lot of load type functions where I need to clean up work tables with a delete, or migrate work table stuff to it's final destination...lot of log activity.....otherwise....

    Code:
    BEGIN TRAN
    
    Some DML...
    
    SELECT @Error=@@Error, @Rowcount = @@ROWCOUNT
    
    IF @Error <> 0
      BEGIN
        -- Some error message
        GOTO sproc_error
        END
    IF @RowCount = 0 -- assume here you expected something
       BEGIN
        -- Some error message
        GOTO sproc_error
        END
    -- continue through sproc using fallthrough logic...
    
    PRINT 'Success'
    
    COMMIT TRAN
    
    Sproc_Exit:
    
    --housekeeping code
    
    Return @rc
    
    Sproc_Error:
    
    PRINT 'NO GOOD'
    
    ROLLBACK TRAN
    
    SELECT @rc = -1
    
    GOTO Proc_Exit
    GO
    Last edited by Brett Kaiser; 10-16-03 at 12:58.
    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.

  4. #4
    Join Date
    Sep 2011
    Posts
    5
    dear guys,

    if i want to make transaction like this in COBOL language, what i must suppose to do? i don't know much about sql syntax in COBOL. Please somebody help me

    thanks a lot.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Which COBOL, OS, and SQL are you using? The combination will determine how to handle and code transaction management.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Sep 2011
    Posts
    5
    Quote Originally Posted by Pat Phelan View Post
    Which COBOL, OS, and SQL are you using? The combination will determine how to handle and code transaction management.

    -PatP
    i'm using AS/400 SMART Ver 2009-04. Can you help to solve my problem?
    Last edited by Bey; 09-12-11 at 01:32.

Posting Permissions

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