Как получить доступ к набору данных в текущей области, сгенерированной вызовом хранимой процедуры в TSQL?

Проблема фона

Простое создание и доступ к данным с фиксированным расположением столбцов. Вы можете создавать локальные таблицы temp вверх и заполнять их, вызывая хранимые процедуры.

С другой стороны, если вы хотите генерировать данные с динамическим расположением столбцов, вы должны, как правило, строить инструкцию SQL динамически и выполнять ее с помощью "exec sp_executesql" . Поскольку макет данных неизвестен во время выполнения, вы не можете создать временную таблицу вверх и один раз внутри оператора exec sp_executesql, любые временные таблицы, созданные там, привязаны к этой области и исчезают, когда вызов возвращается, поэтому гораздо труднее получить доступ к данным (т.е. ваши варианты более ограничены).

Моя специфическая ситуация

У меня есть запрос, которому необходимо получить доступ к данным в динамически сгенерированной таблице.

Таблица генерируется хранимой процедурой, которая динамически создает запрос, сохраняет его в переменной "@sql nvarchar (max)" и запускает ее, вызывая "exec sp_executesql @statement = @sql".

Оператор @sql был чем-то вроде "select * in #temptable from...", но #temptable был уничтожен к моменту возврата "exec sp_executesql" . Быстрое исправление заключается в том, чтобы вместо этого использовать "## temptable" (т.е. Глобальную таблицу temp), поскольку она сохраняется, когда возвращается хранимая процедура, и я могу легко получить к ней доступ в области вызова (поскольку у нее есть известное/статическое имя).

Мне не нравится это решение, потому что глобальные таблицы temp не являются потокобезопасными (имя collistion-wise), и я не хочу связываться с динамически генерируемыми уникальными именами, потому что я просто закончу чтобы использовать более динамический SQL для доступа к ним... который возвращает меня обратно на квадрат, оставляя данные недоступными вне SP.

Я не думаю, что возвращаемые переменные таблицы (через выходные параметры) - это опция (новая для SQL Server 2008), если только это не может быть сделано без необходимости определения типа статической таблицы. Таблицы, созданные моей хранимой процедурой, являются динамическими и зависят от переданных входных параметров.

Встроенные функции, основанные на таблице, не являются опцией, потому что я запускаю циклы кода для построения запроса @sql и вызова "exec sp_executesql" .

Многозначные табличные функции (вместо хранимой процедуры) также не являются опцией, поскольку такая функция должна иметь четко определенный формат таблицы, тогда как я запускаю dyanmic SQL для возврата таблицы с переменное количество столбцов и имен столбцов в зависимости от значений входных параметров.

Все, что я действительно хочу сделать, это выбрать набор результатов динамического запроса в новую таблицу, но мне сложно, поскольку ни одно из вышеизложенных не работает; особенно раздражает то, как локальные временные таблицы не являются локальными для сеанса, но локальны для хранимой процедуры, чтобы они исчезали при возврате. Единственное решение, которое я видел, настаивает на том, что использование OPENROWSET - единственный способ, но я не хочу связываться со строками подключения внутри моей хранимой процедуры по той же причине Я не хочу включать код управления уникальным именем... он просто более сложный, чем он должен быть.

В общем, я просто хочу выполнить динамический SQL, который генерирует набор данных неизвестного формата и сможет легко получить к нему доступ из области вызова.

Ответы

CREATE TABLE #blah (a int)
SELECT *
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE Object_id('tempdb.dbo.' + TABLE_NAME) = Object_id('tempdb.dbo.#blah')
DROP TABLE #blah

DECLARE @sql varchar(max) = '', 
@tmp_global_table varchar(255) = '##global_tmp_' + CONVERT(varchar(36), NEWID())
SET @sql = @sql + 'select * into [' + @tmp_global_table + '] from YOURTABLE'
EXEC(@sql)

EXEC('SELECT * FROM [' + @tmp_global_table + ']')