How to access dataset in current scope generated by a call to a stored procedure in TSQL?
Problem Background
Generating and accessing data of a fixed column layout is easy. You can create local temp tables up-front, and populate them by calling stored procedures.
On the other hand, if you want to generate data with a dynamic column layout, you must generally build an SQL statement dynamically and execute it with "exec sp_executesql". Since the data layout is unknown at run-time, you cannot create a temp-table up-front, and once inside the "exec sp_executesql" statement, any temporary tables created there are bound to that scope and vanish when the call returns, so it's much more difficult to access the data (i.e. your options are more limited).
My Specific Situation
I have a query that needs to access data in a dynamically generated table.
The table is generated by a stored procedure, which dynamically builds a query, stores it in a variable "@sql nvarchar(max)", and runs it by calling "exec sp_executesql @statement = @sql".
The @sql statement was something like "select * into #temptable from...", but #temptable was destroyed by the time "exec sp_executesql" returned. A quick fix for this was to just use "##temptable" instead (i.e. a global temp table), because it survives when the stored procedure returns AND I can easily access it in the calling scope (because it has a known/static name).
I don't like this solution because global temp tables aren't thread-safe (name collistion-wise), and I don't want to have to mess with dynamically-generated unique names, because I'll just end up having to use more dynamic SQL to access them... which puts me right back at square one, leaving the data inaccessible outside the SP.
I don't think returning table variables (through output parameters) is an option (new to SQL Server 2008 too), unless it can be done without having to define a static table type. The tables my stored procedure generates are dynamic, and depend on the input parameter(s) passed.
Inline table-valued functions are not an option, because I'm running code loops to build the @sql query and calling "exec sp_executesql".
Multi-statement table-valued functions (instead of the stored procedure), is also not an option, because such a function must have a well-defined table format, whereas I'm running dyanmic SQL to return a table with a variable number of columns and column names depending on the input parameter values.
All I really want to do is select the result set of the dynamic query into a new table, but I'm finding it difficult, as none of the above works; particularly irritating is how local temporary tables aren't local to the session, but local to the stored procedure so that they vanish upon returning. The only solution I've seen insists that using OPENROWSET is the only way, but I don't want to mess with connection strings inside my stored procedure, for the same reason I don't want to include unique-name-management code... it's just way more complicated than it ought to be.
In summary, I just want to execute dynamic SQL that generates a dataset of an unknown format, and be able to easily access it from the calling scope.
Answer
Create the temp table before the sp_executesql: it will still be in scope for "inner" scopes like the sp_executesql
Change the SQL to do an INSERT rather than SELECT..INTO...
Edit:
Make the table wide enough to cover all options.
Frankly, SQL is designed to work with fixed table definitions: variable output signatures (tables) leads to the problem you have...