Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: Temp Tables Created in Stored Procedures

    Are they unique to a user/session? Like if 2 users simultaneously run the stored procedure?
    TIA!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, temp tables created within a stored procedure can only be accessed within that procedure. Temp tables created within a stored procedure cease to exist when the stored procedure exits.

    -PatP

  3. #3
    Join Date
    Jul 2006
    Posts
    111
    Hi Pat Thanks very much for the reply.
    Just want to be really sure I understand you. If my SP populates the temp table in stages, and say, 2 users ran the SP at slightly different times. User1 is in stage 2 populating the temp table and say has 100 rows inserting 30 additional rows and User2 is only in stage 1 inserting 70 rows at the end of their current stages then User1 and User2 will have 130 rows and 70 rows respectively? As if they have distinct temp tables each.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Exactly correct. If two dozen users each run your stored procedure, and each of them put a unique number of rows into the temp table, then you'd have 24 different temp tables, each with a different set of rows in them.

    -PatP

  5. #5
    Join Date
    Jul 2006
    Posts
    111

    thanks

    i have now peace of mind :-)

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by g11DB
    i have now peace of mind :-)
    where do I get some of that?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Go do something socially unacceptable in front of your aunt. I bet she'll give you a piece of her mind.

    -PatP

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I do not want to drive to Richmond in the rain. Anyways the really scary aunt would more likely act out with violence instead of a tongue lashing and then there is the whole difficult chore of finding something that would truly offend her.

    Oh well I guess I will try the Buddhist Scriptures and meditation again.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Thrasymachus
    where do I get some of that?
    Try looking in dark alleyways for gentlemen wearing trenchcoats...
    George
    Home | Blog

  10. #10
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    i could take that to mean a few different things. none of which would bring me any peace.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Pat Phelan
    Yes, temp tables created within a stored procedure can only be accessed within that procedure.
    The scope is a teeny bit wider than that though.
    Code:
    USE tempdb
    GO
    
    IF EXISTS (SELECT NULL FROM sys.sql_modules WHERE object_id = object_id('dbo.proc_1')) BEGIN
        DROP PROCEDURE dbo.proc_1
    END
    GO
    
    IF EXISTS (SELECT NULL FROM sys.sql_modules WHERE object_id = object_id('dbo.proc_2')) BEGIN
        DROP PROCEDURE dbo.proc_2
    END
    GO
    
    CREATE PROCEDURE dbo.proc_2
    AS
    
        INSERT #t SELECT 1
    
    GO    
    
    CREATE PROCEDURE dbo.proc_1
    AS
    
        CREATE TABLE #t (a INT)
    
        EXEC dbo.proc_2
    
        SELECT    *
        FROM    #t
    
    GO    
    
    EXEC dbo.proc_1
    
    IF EXISTS (SELECT NULL FROM sys.sql_modules WHERE object_id = object_id('dbo.proc_1')) BEGIN
        DROP PROCEDURE dbo.proc_1
    END
    GO
    
    IF EXISTS (SELECT NULL FROM sys.sql_modules WHERE object_id = object_id('dbo.proc_2')) BEGIN
        DROP PROCEDURE dbo.proc_2
    END
    GO

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by microsoft
    Local temporary tables are visible only in the current session
    How do they define a session then?
    George
    Home | Blog

  13. #13
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    local temp tables (#) created within a proc (say proc A) can be passed to other procs called by the creating proc (say proc A1, proc A2, etc), but a totally unrelated proc (proc B) started by another process cannot access the local temp table created by Proc A.

    However, if proc A creates a global temp table (##), then any proc can access that table. When the last access to that table is closed, then and only then will the global temp table be deleted.

    -- This is all just a Figment of my Imagination --

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    How do they define a session then?
    I don't know nowadays with all this connection pooling malarky but essentially a connection = a session.

    SELECT @@SPID gives you your session id. Open up a load of windows in SSMS and execute and you'll get different ids for each (each one over 50).

Posting Permissions

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