• Not sure, folks, but I think the original question was about having temp tables with the same name running in stored procedures that run at the same time.  The answer, Sahana, is that temp tables that start with a single # sign are session sensitive and a call, via EXEC, seems to also be considered to be a "session".  In fact, you shouldn't need to even drop the temp tables... they should drop "auto-magically".

    There are those that say "table variables" should be used instead of temp tables... I agree if the table variables aren't going to carry a large amount of rows, say, over 50,000.  Table variables try to live in memory and if you have the Standard Edition of SQL Server, you are limited to 2GB of memory no matter how much memory is available in the machine.  If SQL runs out of memory because of an oversized table variable, the machine will start spooling to the swap file... all semblence of speed will vanish.

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