Post reply

Stored Proc Block Using BEGIN/END

  • January 11, 2008 at 6:46 am

    #63001

    Hi All,

    Hopefully a very simple question:

    Stored procedures have a BEGIN and END statement around the entire contents of the stored proc. Does that mean that every TSQL statement within the stored proc is turned into a single transaction? If the stored proc contains 1 INSERT followed by 10 UPDATES on that table, will all the processing be taking place in the temp space rather than on the actual table with the results of the 1 INSERT and 10 UPDATES only being reflected in the actual table when that final END statement is reached?

    Thanks, Alan.

  • January 11, 2008 at 7:39 am

    #767104

    No.

    Begin != Begin transaction

    Begin...End are just used to group code together. eg

    IF (condition)

    Begin

    Do lots of things

    End

    Unless you explicitly begin a transaction, SQL works in autocommit, where each statement is in its own transaction and committed automatically once it finishes

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 11, 2008 at 7:48 am

    #767115

    Thanks for your prompt reply Gail. I suspected as much but didn't want to make a fool of myself without checking first!

  • January 11, 2008 at 9:22 pm

    #767384

    The real fact is, you don't need BEGIN/END in a stored procedure. Try it... it's just allowed to make die hard Oracle programmers happy 😛

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply