Post reply

Temp table in stored procedure

  • June 11, 2007 at 3:25 pm

    #179122

    I have created a stored procedure that outputs a record set which is used as the data source for a Crystal Report.

     

    The stored procedure is accumulating data into a temp table (prefaced by #).  My understanding is that the temp table only exists for that connection.

     

    My question is:  If more than one instance of the report is running at the same time, will there be conflict with the temp table? 

  • June 11, 2007 at 4:58 pm

    #712138

    No...

    Each temp table is actually named a bit differently but is transparent to the user... for example, if you have a temp table named #MyHead in a proc, each session that runs the proc actually creates a temp table with a name that looks like this...

    dbo.#MyHead_____________________________________________________________________________________________________________00000000002E

    If another session uses the same proc, the temp table that it makes will have a different "hex" number at the end of the underscores...

    --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)

  • June 11, 2007 at 5:09 pm

    #712140

    Thank you Jeff,

    That's what I needed to hear.

    Katie.

  • June 11, 2007 at 9:00 pm

    #712156

    Maybe I'm just paranoid but I feel the need to point out that 2 instances of the same report ran by the same user constitutes 2 different sesions, hence 2 different temp tables.

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

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy