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

    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

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

Posting Permissions

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