Unanswered: howto use a temporaly table created by an sp?
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.
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.