Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194

    Unanswered: (re)using a temporary table in a stored proc (was "Confusion")

    Hi folks, I have a procedure that pefroms some action and creates the outputs to a temporary table #mytable. I want to call this procedure and take the results from #mytable within the procedure. Can i. If i call #mytable after executing the procedure; won't work. Means that the table gets dropped and doesn't prolong for the session?


    Howdy!

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yes..the local temp table is only around for the length of process...

    A global temp table sticks around as long as it's referenced by any process...

    So you can do something like this...basically wrapping your sprocs with a driver sproc

    Code:
    USE Northwind
    GO
    
    CREATE PROC mySproc00
    AS
      BEGIN
    	UPDATE ##myGlobalTemp99 SET CustomerId = 'VINET'
      END
    GO
    
    CREATE PROC mySproc99
    AS
      BEGIN
    	SELECT * INTO ##myGlobalTemp99 FROM Orders
    
    	SELECT TOP 10 * FROM ##myGlobalTemp99
    
    	EXEC mySproc00
    
    	SELECT TOP 10 * FROM ##myGlobalTemp99
    
    	DROP TABLE ##myGlobalTemp99
    
      END
    GO
    
    EXEC mySproc99
    GO
    
    DROP PROC mySproc00
    DROP PROC mySproc99
    GO
    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.

  3. #3
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194
    Hi, sir. thanx for helpin around. there's a little problem though. The procedure will be executed by many users concurrently; i get error; table already exists when older session persist and another executes the procedure.



    Any other guidlines!


    Howdy!

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    post what the table looks like....and what the sproc is suppose to be doing...
    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.

  5. #5
    Join Date
    Oct 2003
    Posts
    357
    Instead of Temp table, declare table type variable and work with that.

    Madhivanan

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I believe he needs to pass and store data between processing threads...table variables won't let you do that.

    Why I asked for the Table Layout is this.

    Each Process gets it's own unique spid.

    I would create a permanent table, and and a column for the spid.

    Capture the spid, from a driver sproc, and for every row in the process make sure you use that spid.

    Just make sure you clear out the rows for your spid before you start the process.
    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.

  7. #7
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194
    Yes, Brett; that idea helped. thanx.




    Howdy!

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Depending on the volum of activity...I might consider a partitioned view using the spid as the partioning range in different files and on different drives...

    I'll take a look into this...
    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.

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I hate when people are so dogmatic in their statements. What would happen if you run this code? DON'T RUN IT, just answer the questions first!

    create proc sp_1 as selct * from #t
    go
    create proc sp_2 as
    ceate table #t (f1 int null)
    insert #t select 1 union select 2
    exec sp_1
    go
    exec sp_2
    go
    drop proc sp_1, sp_2
    go

    Here, regardless of how many users execute sp_2 simulteneously, they will always see only their temporary tables.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Doooh...it's the same spid

    As long as it's all referenced in the same driver your temp table is isolated..I got confused that it was with separate threads

    Even with that...what I suggested wouldn't buy you anything anyway

    Thanks for pointing that out...
    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.

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by Brett Kaiser
    Doooh...Even with that...what I suggested wouldn't buy you anything anyway...
    So why did you suggest it?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Cause I'm a moron....

    It's like flying down the freeway...I missed the exit

    You could use that method to pass data to a trigger though....
    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.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So if that is not causing the problem he is having, then what is?

    Quote Originally Posted by thebeginner
    If i call #mytable after executing the procedure; won't work.
    This seems to indicate he wants to access the table through a different connection...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by thebeginner
    If i call #mytable after executing the procedure; won't work. Means that the table gets dropped and doesn't prolong for the session?
    Actually it looks like the same connection, unless his definition of a "session" is different...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  15. #15
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by Brett Kaiser
    It's like flying down the freeway...I missed the exit....
    Don't feel bad, I use turn-arounds very often
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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