Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2005
    Posts
    1

    Question Unanswered: howto use a temporaly table created by an sp?

    Hi all!

    I would like to gain data from a temporaly table created by an EXEC command.
    e.g. EXEC('SELECT col1, col2, col3 FROM Table WHERE ...') - that's right.
    But I would like to use it:
    SELECT * FROM _ThisTempTableTheExecCommandHasCreatedRigthNow
    WHERE...

    I know that a function can return a table but an sp cannot.

    How can I do it?

    Thx: Gurmy

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Dynamically executed SQL occurs within its own scope, which is a subscope of the command that executed it. So within the dynamic SQL statement you can reference objects that exist outside of it, but once the statement concludes any temporary objects created by it drop out of scope and are no longer available. The exceptions are Global Temporary Tables (prefixed by a ##), which are available to all users and don't (I think) drop immediately out of scope. The problem is that if you have multiple users, they will all be using the same named global temp table, and will doubtless collide with eachother.
    One method I have used to get around this is to create an empty temporary table in your main procedure, and then select into it in your dynamic SQL statement.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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