Вставить результаты хранимой процедуры во временную таблицу

Как мне это сделать SELECT * INTO [temp table] FROM [stored procedure]? Не FROM [Table]и без определения [temp table]?

Selectвсе данные BusinessLineв в tmpBusLineработает нормально.

select *
into tmpBusLine
from BusinessLine

Я пытаюсь то же самое, но использование, stored procedureкоторое возвращает данные, не совсем то же самое.

select *
into tmpBusLine
from
exec getBusinessLineHistory '16 Mar 2009'

Выходное сообщение:

Сообщение 156, уровень 15, состояние 1, строка 2 Неверный синтаксис рядом с ключевым словом "exec".

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

17.03.2009 10:45:26
С помощью SELECT * INTO [TABLE NAME] вы узнаете столбцы, так как они скопированы из исходной таблицы. Это именно то, что я хочу, если бы я делал то же самое с хранимой процедурой.
Ferdeen 17.03.2009 16:49:53
Triynko 2.06.2011 15:14:39
Просто хочу указать, что «select * into tmpBusLine» создает постоянную таблицу. Вы, вероятно, хотите "выбрать * в #tmpBusLine". Я уверен, что оригинальный постер уже обнаружил это, но это может помочь другим, которые находят это сообщение, так как это лучший результат в настоящее время для поиска "выберите в таблице временных параметров"
ktam33 25.07.2014 19:53:08
Я не знаю, была ли это решена, но причина, по которой вы получаете ошибку, связана с ключевым словом from.
Wes Palmer 1.12.2016 16:35:39
Microsoft необходимо добавить SELECT * INTO EXEC! Пожалуйста!
kjmerf 25.05.2018 17:26:33
29 ОТВЕТОВ

В SQL Server 2005 вы можете использовать, INSERT INTO ... EXECчтобы вставить результат хранимой процедуры в таблицу. Из документации MSDNINSERT (фактически для SQL Server 2000):

--INSERT...EXECUTE procedure example
INSERT author_sales EXECUTE get_author_sales
294
25.07.2013 15:54:01
Это требует, чтобы авторы были определены заранее. Я пытаюсь избежать этого. Спасибо.
Ferdeen 17.03.2009 10:53:29
Я так и думал. Так полезно Вставлять в таблицы tmp на лету, но не так полезно, если вам нужно знать структуру набора данных, возвращенную из сохраненного процесса. Спасибо за помощь.
Ferdeen 17.03.2009 10:57:14
Здесь есть хорошая статья: msdn.microsoft.com/en-us/library/aa175921.aspx
Rich Andrews 17.03.2009 11:07:18
Чтобы использовать ту же схему, вы можете сделать копию следующим образом: выберите top 0 * в tempTable из realTable ( stackoverflow.com/a/9206463/73794 )
Even Mien 11.04.2016 17:34:56
@EvenMien Я на мгновение обрадовался, увидев ваш комментарий ... но, к сожалению, это работает, только если результаты вашего процесса фактически отражают реальную таблицу :(
BVernon 22.04.2020 22:33:29
EXEC sp_serveroption 'YOURSERVERNAME', 'DATA ACCESS', TRUE

SELECT  *
INTO    #tmpTable
FROM    OPENQUERY(YOURSERVERNAME, 'EXEC db.schema.sproc 1')
131
21.09.2018 15:27:26
Получите сообщение «Msg 208, уровень 16, состояние 1, строка 1». Недопустимое имя объекта «tmpBusLine» (вероятно, так как оно не определено
Ferdeen 17.03.2009 10:54:45
@Ferds: извините, сначала не понял ваш запрос. Обновлено с другим решением.
Quassnoi 4.08.2009 15:35:13
Отличное решение. Одно предостережение: вам нужно включить «Доступ к данным» на вашем сервере: EXEC sp_serveroption «TheServerName», «Доступ к
jcollum 23.12.2009 17:39:19
Вам также необходимо разрешить удаленный доступ к серверу. Это будет иметь последствия для безопасности.
BraveNewMath 6.05.2013 18:35:56
Это не будет работать, если целевая хранимая процедура использует временные таблицы
Sal 23.01.2017 16:13:17

Когда хранимая процедура возвращает много столбцов, и вы не хотите вручную «создавать» временную таблицу для хранения результата, я обнаружил, что самый простой способ - перейти в хранимую процедуру и добавить предложение «в» в последний оператор select и добавьте 1 = 0 к предложению where.

Запустите хранимую процедуру один раз, вернитесь и удалите только что добавленный код SQL. Теперь у вас будет пустая таблица, соответствующая результату хранимой процедуры. Вы можете либо «скрипт таблицы как создать» для временной таблицы, либо просто вставить непосредственно в эту таблицу.

105
23.05.2011 09:50:18
+1, отличное предложение. Вы могли бы даже добавить в sproc быструю необязательную переменную с именем @TableCreate или что-то подобное, что, когда не равно NULL, выполнить шаги выше Не требует замены sproc, тогда как он настроен.
Ian Roke 27.08.2009 15:24:20
@dotjoe Вы делаете SELECT INTOвременную таблицу и создаете таблицу сценариев как создать из временной таблицы? Временные таблицы появляются, tempdbно я не могу щелкнуть правой кнопкой мыши и создать скрипт. Любая помощь приветствуется.
DotnetDude 24.04.2012 18:10:23
@DotNetDude вы можете select ... into new_tableнеявно создать фактическую таблицу.
dotjoe 24.04.2012 19:09:01
Затем получите грубое определение столбца из пустой схемы таблицы; замените '...' в конце на законное имя TABLE_NAME:declare @s varchar(max)='';select @s=@s+','+COLUMN_NAME+' '+DATA_TYPE+isnull('('+case CHARACTER_MAXIMUM_LENGTH when -1 then 'max' else cast(CHARACTER_MAXIMUM_LENGTH as varchar(10))end+')','')from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='...';select @s
user423430 15.11.2017 22:10:18
Это лучшее решение!
Lucas925 3.10.2019 18:13:27

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

CREATE TABLE #tmpBus
(
   COL1 INT,
   COL2 INT
)

INSERT INTO #tmpBus
Exec SpGetRecords 'Params'
620
12.07.2014 20:56:17
Я думаю, что смысл заключался в том, чтобы сгенерировать схему без явного ее объявления.
Craig 26.04.2012 16:18:37
Мне было бы интересно узнать, в чем разница между этим решением @Aaron Alton и выше. Этот кажется гораздо проще, но я не уверен в любых других последствиях.
funkymushroom 1.06.2012 17:57:37
Это будет работать, но если вы когда-нибудь добавите дополнительные столбцы в хранимую процедуру SpGetRecords, это взорвется.
Brady Holt 23.01.2014 16:15:31
Вы получаете только один INSERT INTO EXEC на стек вызовов. SpGetRecords и любой другой вызываемый им процесс не могут использовать эту стратегию в своем собственном коде. Это может удивить сопровождающих SpGetRecords.
Matt Stephenson 9.04.2014 05:41:54
Это не отвечает на вопрос вообще, и я не понимаю, почему это так проголосовало? В OP явно указано «без определения [временная таблица]», и в самой первой строке есть оператор создания временной таблицы.
NickG 24.04.2015 09:30:50

Вы можете использовать OPENROWSET для этого. Посмотри. Я также включил код sp_configure, чтобы включить специальные распределенные запросы, если он еще не включен.

CREATE PROC getBusinessLineHistory
AS
BEGIN
    SELECT * FROM sys.databases
END
GO

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC getBusinessLineHistory')

SELECT * FROM #MyTempTable
701
17.07.2013 17:03:49
Это правильный способ сделать это. OPENROWSET - практически единственный способ обработать результаты хранимой процедуры как табличное выражение.
Rob Farley 5.08.2009 13:24:36
Это кажется немного громоздким, чтобы просто вставить в таблицу. Много настроек сделать. Также, когда я попробовал это, я получил "Msg 7357, Уровень 16, Состояние 2, Строка 1, Не могу обработать объект" EXEC GetPartyAnalysisData 146 ". Поставщик OLE DB" SQLNCLI "для связанного сервера" (null) "указывает, что любой объект имеет нет столбцов или текущий пользователь не имеет разрешения на этот объект. " Так что вам нужно установить связанный сервер ...
Ferdeen 10.08.2009 12:18:10
Вам не нужен связанный сервер, но вам нужно правильно указать строку подключения ... а также указать полный путь к хранимой процедуре, включая имя базы данных и владельца sp.
MartW 11.08.2009 12:30:45
eeeeew! ссылка на тот же сервер? противно. определенно больше взломать, чем вручную создавать временную таблицу
Tim Abell 17.11.2010 16:16:28
Я согласен, что это взлом, и, вероятно, его следует избегать, если только вы не прислонились к стене. Изменение sp на функцию, вероятно, является лучшим углом. ИМХО.
greg 16.04.2013 20:20:10

Это ответ на слегка измененную версию вашего вопроса. Если вы можете отказаться от использования хранимой процедуры для пользовательской функции, вы можете использовать встроенную табличную пользовательскую функцию. По сути, это хранимая процедура (будет принимать параметры), которая возвращает таблицу как набор результатов; и, следовательно, будет хорошо с заявлением INTO.

Вот хорошая быстрая статья об этом и других пользовательских функциях. Если у вас все еще есть потребность в хранимой процедуре, вы можете заключить встроенную табличную пользовательскую функцию в хранимую процедуру. Хранимая процедура просто передает параметры, когда она вызывает select * из встроенной пользовательской функции с табличным значением.

Так, например, у вас будет встроенная пользовательская функция с табличным значением для получения списка клиентов для определенного региона:

CREATE FUNCTION CustomersByRegion 
(  
    @RegionID int  
)
RETURNS TABLE 
AS
RETURN 
  SELECT *
  FROM customers
  WHERE RegionID = @RegionID
GO

Затем вы можете вызвать эту функцию, чтобы получить такие результаты:

SELECT * FROM CustomersbyRegion(1)

Или сделать ВЫБОР В:

SELECT * INTO CustList FROM CustomersbyRegion(1)

Если вам все еще нужна хранимая процедура, оберните эту функцию следующим образом:

CREATE PROCEDURE uspCustomersByRegion 
(  
    @regionID int  
)
AS
BEGIN
     SELECT * FROM CustomersbyRegion(@regionID);
END
GO

Я думаю, что это самый «безрукий» метод для получения желаемых результатов. Он использует существующие функции, так как они были предназначены для использования без дополнительных сложностей. Вложив встроенную пользовательскую функцию с табличным значением в хранимую процедуру, вы получаете доступ к этой функции двумя способами. Плюс! У вас есть только одна точка обслуживания для реального кода SQL.

Было предложено использовать OPENROWSET, но это не то, для чего предназначалась функция OPENROWSET (из Books Online):

Содержит всю информацию о соединении, необходимую для доступа к удаленным данным из источника данных OLE DB. Этот метод является альтернативой доступу к таблицам на связанном сервере и является одноразовым, специальным методом подключения и доступа к удаленным данным с использованием OLE DB. Для более частых ссылок на источники данных OLE DB используйте вместо этого связанные серверы.

Использование OPENROWSET выполнит работу, но потребует дополнительных затрат для открытия локальных соединений и сортировки данных. Это также может быть не вариант во всех случаях, поскольку требует специального разрешения запроса, которое представляет угрозу безопасности и, следовательно, может быть нежелательным. Кроме того, подход OPENROWSET исключает использование хранимых процедур, возвращающих более одного набора результатов. Этого можно достичь, заключив в одну хранимую процедуру несколько встроенных пользовательских функций табличного значения.

194
17.01.2018 20:36:36
+1 Табличная функция - подходящее решение. Следует обратить внимание на незначительные недостатки: табличная функция является дополнительным объектом базы данных, и может потребоваться предоставить ей привилегии.
spencer7593 11.08.2010 18:29:57
Люблю решение. Одна небольшая загвоздка, с которой я столкнулся, заключается в том, что мой стол не может иметь порядок, где он может быть в хранимой процедуре. Хорошо, я разберусь с этим
mrwaim 4.03.2011 16:49:51
Еще одна загвоздка - «Невозможно получить доступ к временным таблицам из функции»
mrwaim 5.03.2011 21:32:58
Первоначальный вопрос заключается в том, как нам создать временную таблицу с результатами sp. Это хороший пример, но этот вопрос не решается
greg 16.04.2013 20:16:57
greg, первая строка в моем ответе гласит: «Это ответ на слегка измененную версию вашего вопроса». Ваш комментарий излишний.
Christian Loris 28.05.2013 12:51:18

Ваша хранимая процедура только получает данные или изменяет их тоже? Если он используется только для извлечения, вы можете преобразовать хранимую процедуру в функцию и использовать выражения общих таблиц (CTE), не объявляя ее следующим образом:

with temp as (
    select * from dbo.fnFunctionName(10, 20)
)
select col1, col2 from temp

Однако все, что необходимо извлечь из CTE, следует использовать только в одном операторе. Вы не можете сделать with temp as ...и попытаться использовать его после нескольких строк SQL. Вы можете иметь несколько CTE в одном операторе для более сложных запросов.

Например,

with temp1020 as (
    select id from dbo.fnFunctionName(10, 20)
),
temp2030 as (
    select id from dbo.fnFunctionName(20, 30)
)
select * from temp1020 
where id not in (select id from temp2030)
48
4.12.2014 22:26:03
Это не временные таблицы, это CTE. technet.microsoft.com/en-us/library/...
yucer 3.12.2014 13:48:57
Спасибо @yucer ... Я думаю, я не знал, что тогда они назывались CTE :)
SO User 4.12.2014 22:24:52

Я нашел Передачу массивов / таблиц данных в хранимые процедуры, которые могут дать вам другое представление о том, как вы можете решить свою проблему.

Ссылка предлагает использовать параметр типа Image для перехода в хранимую процедуру. Затем в хранимой процедуре изображение преобразуется в табличную переменную, содержащую исходные данные.

Может быть, есть способ, которым это можно использовать с временной таблицей.

11
12.07.2014 21:00:25
Это больше не требуется в версиях Sql2008 и более поздних версиях с введением параметров табличных значений . Теперь вы можете напрямую передать набор данных .net или объект с данными для хранимой процедуры sql, выполнив преобразование в байт, как указано в ссылке выше
EndlessSpace 10.06.2011 19:24:47

Другой метод - создать тип и использовать PIPELINED, чтобы затем вернуть свой объект. Однако это ограничено знанием столбцов. Но у него есть преимущество:

SELECT * 
FROM TABLE(CAST(f$my_functions('8028767') AS my_tab_type))
9
23.05.2011 09:54:47
Что это? Похоже, это не имеет ничего общего с SQL Server, о котором идет речь в этом вопросе
Martin Smith 10.01.2020 18:37:17

Quassnoi поставил меня большую часть пути туда, но одна вещь отсутствовала:

**** Мне нужно было использовать параметры в хранимой процедуре. ****

И OPENQUERY не позволяет этому случиться:

Таким образом, я нашел способ работать с системой, а также не нужно делать определение таблицы настолько жестким и переопределять его внутри другой хранимой процедуры (и, конечно, воспользоваться шансом, что она может сломаться)!

Да, вы можете динамически создать определение таблицы, возвращенное из хранимой процедуры, с помощью оператора OPENQUERY с фиктивными переменными (пока набор NO RESULT SET возвращает то же количество полей и в той же позиции, что и набор данных с хорошими данными).

После создания таблицы вы можете использовать хранимую процедуру exec во временной таблице в течение всего дня.


И чтобы отметить (как указано выше), вы должны включить доступ к данным,

EXEC sp_serveroption 'MYSERVERNAME', 'DATA ACCESS', TRUE

Код:

declare @locCompanyId varchar(8)
declare @locDateOne datetime
declare @locDateTwo datetime

set @locDateOne = '2/11/2010'
set @locDateTwo = getdate()

--Build temporary table (based on bogus variable values)
--because we just want the table definition and
--since openquery does not allow variable definitions...
--I am going to use bogus variables to get the table defintion.

select * into #tempCoAttendanceRpt20100211
FROM OPENQUERY(DBASESERVER,
  'EXEC DATABASE.dbo.Proc_MyStoredProc 1,"2/1/2010","2/15/2010 3:00 pm"')

set @locCompanyId = '7753231'

insert into #tempCoAttendanceRpt20100211
EXEC DATABASE.dbo.Proc_MyStoredProc @locCompanyId,@locDateOne,@locDateTwo

set @locCompanyId = '9872231'

insert into #tempCoAttendanceRpt20100211
EXEC DATABASE.dbo.Proc_MyStoredProc @locCompanyId,@locDateOne,@locDateTwo

select * from #tempCoAttendanceRpt20100211
drop table #tempCoAttendanceRpt20100211

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

Поиск тегов ссылок:

  • Хранимая процедура SQL 2005 во временную таблицу

  • openquery с хранимой процедурой и переменными 2005

  • openquery с переменными

  • выполнить хранимую процедуру во временной таблице

Обновление: это не будет работать с временными таблицами, поэтому мне пришлось прибегнуть к созданию временной таблицы вручную.

Заметим, что это не будет работать с временными таблицами , http://www.sommarskog.se/share_data.html#OPENQUERY

Ссылка: Следующим шагом является определение LOCALSERVER. В примере это может выглядеть как ключевое слово, но на самом деле это только имя. Вот как вы это делаете:

sp_addlinkedserver @server = 'LOCALSERVER',  @srvproduct = '',
                   @provider = 'SQLOLEDB', @datasrc = @@servername

Чтобы создать связанный сервер, вы должны иметь разрешение ALTER ANY SERVER или быть участником любой из фиксированных ролей сервера sysadmin или setupadmin.

OPENQUERY открывает новое соединение с SQL Server. Это имеет некоторые последствия:

Процедура, которую вы вызываете с помощью OPENQUERY, не может ссылаться на временные таблицы, созданные в текущем соединении.

Новое соединение имеет свою собственную базу данных по умолчанию (определенную с помощью sp_addlinkedserver, по умолчанию это master), поэтому все спецификации объекта должны включать имя базы данных.

Если у вас есть открытая транзакция и вы удерживаете блокировки, когда вызываете OPENQUERY, вызываемая процедура не может получить доступ к тому, что вы заблокировали. То есть, если вы не будете осторожны, вы заблокируете себя.

Подключение не бесплатное, поэтому снижается производительность.

32
1.06.2011 01:12:48
Если вы не знаете имя своего сервера, используйте SELECT @@SERVERNAME. Вы можете также использоватьEXEC sp_serveroption @@SERVERNAME, 'DATA ACCESS', TRUE
Contango 1.07.2017 13:43:11

Код

CREATE TABLE #T1
(
    col1 INT NOT NULL,
    col2 NCHAR(50) NOT NULL,
    col3 TEXT NOT NULL,
    col4 DATETIME NULL,
    col5 NCHAR(50) NULL,
    col6 CHAR(2) NULL,
    col6 NCHAR(100) NULL,
    col7 INT NULL,
    col8 NCHAR(50) NULL,
    col9 DATETIME NULL,
    col10 DATETIME NULL
)

DECLARE @Para1 int
DECLARE @Para2 varchar(32)
DECLARE @Para3 varchar(100)
DECLARE @Para4 varchar(15)
DECLARE @Para5 varchar (12)
DECLARE @Para6 varchar(1)
DECLARE @Para7 varchar(1)


SET @Para1 = 1025
SET @Para2 = N'6as54fsd56f46sd4f65sd'
SET @Para3 = N'XXXX\UserName'
SET @Para4 = N'127.0.0.1'
SET @Para5 = N'XXXXXXX'
SET @Para6 = N'X'
SET @Para7 = N'X'

INSERT INTO #T1
(
    col1,
    col2,
    col3,
    col4,
    col5,
    col6,
    col6,
    col7,
    col8,
    col9,
    col10,
)
EXEC [dbo].[usp_ProcedureName] @Para1, @Para2, @Para3, @Para4, @Para5, @Para6, @Para6

Надеюсь, это поможет. Пожалуйста, квалифицируйтесь соответствующим образом.

13
1.06.2011 01:26:04
declare @temp table
(
    name varchar(255),
    field varchar(255),
    filename varchar(255),
    filegroup varchar(255),
    size varchar(255),
    maxsize varchar(255),
    growth varchar(255),
    usage varchar(255)
);
INSERT @temp  Exec sp_helpfile;
select * from @temp;
66
1.06.2011 01:26:40
не отвечает на оригинальный вопрос OP, вставка без определения временной таблицы в первую очередь.
t.durden 19.05.2017 15:31:31

Этот сохраненный процесс выполняет работу:

CREATE PROCEDURE [dbo].[ExecIntoTable]
(
    @tableName          NVARCHAR(256),
    @storedProcWithParameters   NVARCHAR(MAX)
)
AS
BEGIN
    DECLARE @driver         VARCHAR(10)
    DECLARE @connectionString   NVARCHAR(600)
    DECLARE @sql            NVARCHAR(MAX)
    DECLARE @rowsetSql      NVARCHAR(MAX)

    SET @driver = '''SQLNCLI'''

    SET @connectionString = 
        '''server=' + 
            CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(256)) + 
            COALESCE('\' + CAST(SERVERPROPERTY('InstanceName') AS NVARCHAR(256)), '') + 
        ';trusted_connection=yes'''

    SET @rowsetSql = '''EXEC ' + REPLACE(@storedProcWithParameters, '''', '''''') + ''''

    SET @sql = '
SELECT
    *
INTO 
    ' + @tableName + ' 
FROM
    OPENROWSET(' + @driver + ',' + @connectionString + ',' + @rowsetSql + ')'

    EXEC (@sql)
END
GO

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

Если вы хотите, чтобы он работал с временной таблицей, вам нужно будет использовать ##GLOBALтаблицу и затем удалить ее.

23
7.03.2014 14:30:47

Если таблица результатов вашего хранимого процесса слишком сложна, чтобы вручную ввести оператор «создать таблицу», и вы не можете использовать OPENQUERY OR OPENROWSET, вы можете использовать sp_help для генерации списка столбцов и типов данных. Когда у вас есть список столбцов, вам нужно просто отформатировать его в соответствии с вашими потребностями.

Шаг 1: Добавьте «в #temp» к выходному запросу (например, «выберите [...] в #temp from [...]»).

Самый простой способ - это напрямую редактировать выходной запрос в proc. Если вы не можете изменить сохраненный процесс, вы можете скопировать содержимое в новое окно запроса и изменить его там.

Шаг 2: Запустите sp_help для временной таблицы. (например, "exec tempdb..sp_help #temp")

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

Шаг 3: Скопируйте столбцы и типы данных в оператор создания таблицы

У меня есть лист Excel, который я использую для форматирования вывода sp_help в оператор «создать таблицу». Вам не нужно ничего такого, что нужно, просто скопируйте и вставьте в свой редактор SQL. Используйте имена, размеры и типы столбцов для создания оператора «Create table #x [...]» или «Declare @x Table [...]», который можно использовать для ВСТАВКИ результатов хранимой процедуры.

Шаг 4: Вставить во вновь созданную таблицу

Теперь у вас будет запрос, похожий на другие решения, описанные в этой теме.

DECLARE @t TABLE 
(
   --these columns were copied from sp_help
   COL1 INT,
   COL2 INT   
)

INSERT INTO @t 
Exec spMyProc 

Этот метод также можно использовать для преобразования временной таблицы ( #temp) в табличную переменную ( @temp). Хотя это может быть больше шагов, чем просто написание create tableоператора, это предотвращает ручные ошибки, такие как опечатки и несоответствия типов данных в больших процессах. Отладка опечатки может занять больше времени, чем написание запроса.

48
27.05.2015 21:55:52
  1. Я создаю таблицу со следующей схемой и данными.
  2. Создайте хранимую процедуру.
  3. Теперь я знаю, каков результат моей процедуры, поэтому я выполняю следующий запрос.

    CREATE TABLE [dbo].[tblTestingTree](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [ParentId] [int] NULL,
        [IsLeft] [bit] NULL,
        [IsRight] [bit] NULL,
    CONSTRAINT [PK_tblTestingTree] PRIMARY KEY CLUSTERED
    (
        [Id] ASC
    ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET IDENTITY_INSERT [dbo].[tblTestingTree] ON
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (1, NULL, NULL, NULL)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (2, 1, 1, NULL)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (3, 1, NULL, 1)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (4, 2, 1, NULL)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (5, 2, NULL, 1)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (6, 3, 1, NULL)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (7, 3, NULL, 1)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (8, 4, 1, NULL)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (9, 4, NULL, 1)
    INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (10, 5, 1, NULL)
    
    SET IDENTITY_INSERT [dbo].[tblTestingTree] OFF

    VALUES (10, 5, 1, NULL) SET IDENTITY_INSERT [dbo]. [TblTestingTree] Вкл.

    create procedure GetDate
    as
    begin
        select Id,ParentId from tblTestingTree
    end
    
    create table tbltemp
    (
        id int,
        ParentId int
    )
    insert into tbltemp
    exec GetDate
    
    select * from tbltemp;
17
18.08.2018 12:51:24

Если OPENROWSET вызывает у вас проблемы, с 2012 года существует другой путь; использовать sys.dm_exec_describe_first_result_set_for_object, как упомянуто здесь: Получить имена столбцов и типы хранимых процедур?

Сначала создайте эту хранимую процедуру, чтобы сгенерировать SQL для временной таблицы:

CREATE PROCEDURE dbo.usp_GetStoredProcTableDefinition(
    @ProcedureName  nvarchar(128),
    @TableName      nvarchar(128),
    @SQL            nvarchar(max) OUTPUT
)
AS
SET @SQL = 'CREATE TABLE ' + @tableName + ' ('

SELECT @SQL = @SQL + '['+name +'] '+ system_type_name +''  + ','
        FROM sys.dm_exec_describe_first_result_set_for_object
        (
          OBJECT_ID(@ProcedureName), 
          NULL
        );

--Remove trailing comma
SET @SQL = SUBSTRING(@SQL,0,LEN(@SQL))    
SET @SQL =  @SQL +')'

Чтобы использовать процедуру, вызовите ее следующим образом:

DECLARE     @SQL    NVARCHAR(MAX)

exec dbo.usp_GetStoredProcTableDefinition
    @ProcedureName='dbo.usp_YourProcedure',
    @TableName='##YourGlobalTempTable',@SQL = @SQL OUTPUT

INSERT INTO ##YourGlobalTempTable
EXEC    [dbo].usp_YourProcedure

select * from ##YourGlobalTempTable

Обратите внимание, что я использую глобальную временную таблицу. Это связано с тем, что при использовании EXEC для запуска динамического SQL создается собственный сеанс, поэтому обычная временная таблица выходит за рамки любого последующего кода. Если глобальная временная таблица является проблемой, вы можете использовать обычную временную таблицу, но любой последующий SQL должен быть динамическим, то есть также выполняемым оператором EXEC.

35
2.10.2019 09:01:56
Вы забыли создать таблицу из @SQL.
Trisped 6.07.2015 19:38:31

Я столкнулся с той же проблемой, и вот что я сделал для этого по предложению Павла . Основная часть здесь заключается в использовании, NEWID()чтобы избежать одновременного запуска процедур / сценариев несколькими пользователями, что создает боль для глобальной временной таблицы.

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 + ']')
10
23.05.2017 11:33:27

Я бы сделал следующее

  1. Создать (преобразовать SP в) UDF (табличное значение UDF).

  2. select * into #tmpBusLine from dbo.UDF_getBusinessLineHistory '16 Mar 2009'

-5
14.03.2015 00:12:23
Могут быть некоторые препятствия, чтобы сделать ваш первый шаг. Например, если исходный SP использует временные таблицы. UDF не могут использовать временные таблицы.
yucer 7.12.2014 01:48:07

Самое простое решение:

CREATE TABLE #temp (...);

INSERT INTO #temp
EXEC [sproc];

Если вы не знаете схему, вы можете сделать следующее. Обратите внимание, что в этом методе существуют серьезные угрозы безопасности.

SELECT * 
INTO #temp
FROM OPENROWSET('SQLNCLI', 
                'Server=localhost;Trusted_Connection=yes;', 
                'EXEC [db].[schema].[sproc]')
125
31.01.2018 15:16:13
если я не знаю столбец возвращенного набора результатов, то ??? Я имею в виду столбец может отличаться. так как вставить результат в временную таблицу ???
SHEKHAR SHETE 6.07.2016 11:47:18
Вы можете использовать OPENQUERY, но это не рекомендуется, так как идет с недостатками безопасности.
Tigerjz32 7.07.2016 19:26:53
«если я не знаю столбец возвращаемого набора результатов, то», то вы не можете использовать его в своей логике. Как вы будете использовать данные, если не знаете, что это такое?
Adriaan Davel 20.07.2016 09:12:35
@AdriaanDavel Я согласен с вами, что вы всегда должны знать свои данные (лучшая практика), однако он может сказать, что бывают случаи, когда sproc возвращает динамические столбцы, и вы не всегда знаете, как будет выглядеть схема. В этом случае вы можете использовать OPENROWSET для вставки и создания таблицы на лету. Однако при этом существуют очевидные угрозы безопасности ...
Tigerjz32 17.08.2016 14:22:04
@nurettin иногда вы не знаете, что будет возвращать хранимая процедура. Что происходит в этом случае? Как вы можете создать временную таблицу (если вы не знаете, что будет возвращать хранимая процедура) и вставить в нее хранимую процедуру?
Tigerjz32 31.10.2017 15:54:44

Если вам известны параметры, которые передаются, и если у вас нет доступа к программе sp_configure, отредактируйте хранимую процедуру с этими параметрами, и ее можно сохранить в глобальной таблице ##.

4
24.03.2015 09:01:55

Чтобы вставить первый набор записей хранимой процедуры во временную таблицу, вам необходимо знать следующее:

  1. только первый набор строк хранимой процедуры может быть вставлен во временную таблицу
  2. хранимая процедура не должна выполнять динамический оператор T-SQL ( sp_executesql)
  3. сначала нужно определить структуру временной таблицы

Вышеупомянутое может выглядеть как ограничение, но ИМХО это вполне имеет смысл - если вы используете, sp_executesqlвы можете один раз вернуть два столбца и один раз десять, а если у вас есть несколько наборов результатов, вы также не можете вставить их в несколько таблиц - вы можете вставить максимум в двух таблицах в одном операторе T-SQL (с использованием OUTPUTпредложения и без триггеров).

Таким образом, проблема заключается в том, как определить структуру временной таблицы перед выполнением EXEC ... INTO ...оператора.

Первый работает с, в OBJECT_IDто время как второй и третий работают также со специальными запросами. Я предпочитаю использовать DMV вместо sp, поскольку вы можете использовать CROSS APPLYи создавать временные определения таблиц для нескольких процедур одновременно.

SELECT p.name, r.* 
FROM sys.procedures AS p
CROSS APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r;

Также обратите внимание на system_type_nameполе, так как оно может быть очень полезным. В нем хранится полное определение столбца. Например:

smalldatetime
nvarchar(max)
uniqueidentifier
nvarchar(1000)
real
smalldatetime
decimal(18,2)

и вы можете использовать его напрямую в большинстве случаев для создания определения таблицы.

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


Обратите внимание, что вышеприведенные объекты не могут определить данные первого результирующего набора в некоторых случаях, например, когда выполняются динамические операторы T-SQL или временные таблицы используются в хранимой процедуре.

17
4.01.2018 08:33:30
практическое наблюдение за ограничениями: если вам нужно вставить вывод некоторого sp (давайте назовем его SP_LEVEL_0) в временную таблицу, динамически созданную с использованием вышеупомянутого подхода в другом sp (давайте назовем его SP_LEVEL_1), вы не сможете сделать тот же трюк для вывода этого SP_LEVEL_1 в какая-то другая временная таблица в SP_LEVEL_2
nahab 22.09.2016 14:12:37

Если запрос не содержит параметра, используйте OpenQueryelse use OpenRowset.

Основной вещью было бы создать схему согласно хранимой процедуре и вставить в эту таблицу. например:

DECLARE @abc TABLE(
                  RequisitionTypeSourceTypeID INT
                , RequisitionTypeID INT
                , RequisitionSourcingTypeID INT
                , AutoDistOverride INT
                , AllowManagerToWithdrawDistributedReq INT
                , ResumeRequired INT
                , WarnSupplierOnDNRReqSubmission  INT
                , MSPApprovalReqd INT
                , EnableMSPSupplierCounterOffer INT
                , RequireVendorToAcceptOffer INT
                , UseCertification INT
                , UseCompetency INT
                , RequireRequisitionTemplate INT
                , CreatedByID INT
                , CreatedDate DATE
                , ModifiedByID INT
                , ModifiedDate DATE
                , UseCandidateScheduledHours INT
                , WeekEndingDayOfWeekID INT
                , AllowAutoEnroll INT
                )
INSERT INTO @abc
EXEC [dbo].[usp_MySp] 726,3
SELECT * FROM @abc
15
8.09.2015 06:28:21

Если вам повезло иметь SQL 2012 или выше, вы можете использовать dm_exec_describe_first_result_set_for_object

Я только что отредактировал sql, предоставленный gotqn. Спасибо, получил.

Это создает глобальную временную таблицу с именем, совпадающим с именем процедуры. Временная таблица может позже использоваться по мере необходимости. Только не забудьте бросить его перед повторным выполнением.

    declare @procname nvarchar(255) = 'myProcedure',
            @sql nvarchar(max) 

    set @sql = 'create table ##' + @procname + ' ('
    begin
            select      @sql = @sql + '[' + r.name + '] ' +  r.system_type_name + ','
            from        sys.procedures AS p
            cross apply sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r
            where       p.name = @procname

            set @sql = substring(@sql,1,len(@sql)-1) + ')'
            execute (@sql)
            execute('insert ##' + @procname + ' exec ' + @procname)
    end
24
28.02.2018 07:04:16
Отлично! Только одно замечание: используйте sys.all_objectsвместо, sys.proceduresесли вы хотите сделать это для встроенных хранимых процедур.
Gert Arnold 11.05.2018 11:35:14
Это также не удастся, если SP использует временные таблицы внутри него. (но очень удобно иметь это в качестве арсенала в вашем арсенале)
Trubs 20.06.2018 03:15:06

Это можно сделать в SQL Server 2014+, если хранимая процедура возвращает только одну таблицу. Если кто-нибудь найдет способ сделать это для нескольких столов, я бы хотел узнать об этом.

DECLARE @storedProcname NVARCHAR(MAX) = ''
SET @storedProcname = 'myStoredProc'

DECLARE @strSQL AS VARCHAR(MAX) = 'CREATE TABLE myTableName '

SELECT @strSQL = @strSQL+STUFF((
SELECT ',' +name+' ' + system_type_name 
FROM sys.dm_exec_describe_first_result_set_for_object (OBJECT_ID(@storedProcname),0)
FOR XML PATH('')
),1,1,'(') + ')'

EXEC (@strSQL)

INSERT INTO myTableName

EXEC ('myStoredProc @param1=1, @param2=2')

SELECT * FROM myTableName

DROP TABLE myTableName

Это извлекает определение возвращаемой таблицы из системных таблиц и использует его для создания временной таблицы. Затем вы можете заполнить его из хранимой процедуры, как указано выше.

Есть также варианты этого, которые также работают с динамическим SQL.

3
26.11.2019 17:39:03

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

GO
create procedure #TempTableForSP(@tableId int, @procedureId int)  
as   
begin  
    declare @tableName varchar(max) =  (select name  
                                        from tempdb.sys.tables 
                                        where object_id = @tableId
                                        );    
    declare @tsql nvarchar(max);    
    declare @tempId nvarchar(max) = newid();      
    set @tsql = '    
    declare @drop nvarchar(max) = (select  ''alter table tempdb.dbo.' + @tableName 
            +  ' drop column ''  + quotename(c.name) + '';''+ char(10)  
                                   from tempdb.sys.columns c   
                                   where c.object_id =  ' + 
                                         cast(@tableId as varchar(max)) + '  
                                   for xml path('''')  
                                  )    
    alter table tempdb.dbo.' + @tableName + ' add ' + QUOTENAME(@tempId) + ' int;
    exec sp_executeSQL @drop;    
    declare @add nvarchar(max) = (    
                                select ''alter table ' + @tableName 
                                      + ' add '' + name 
                                      + '' '' + system_type_name 
                           + case when d.is_nullable=1 then '' null '' else '''' end 
                                      + char(10)   
                              from sys.dm_exec_describe_first_result_set_for_object(' 
                               + cast(@procedureId as varchar(max)) + ', 0) d  
                                order by column_ordinal  
                                for xml path(''''))    

    execute sp_executeSQL  @add;    
    alter table '  + @tableName + ' drop column ' + quotename(@tempId) + '  ';      
    execute sp_executeSQL @tsql;  
end         
GO

create table #exampleTable (pk int);

declare @tableId int = object_Id('tempdb..#exampleTable')
declare @procedureId int = object_id('examplestoredProcedure')

exec #TempTableForSP @tableId, @procedureId;

insert into #exampleTable
exec examplestoredProcedure

Обратите внимание, что это не будет работать, если sys.dm_exec_describe_first_result_set_for_object не может определить результаты хранимой процедуры (например, если она использует временную таблицу).

1
10.10.2018 15:11:51

Это простой двухэтапный процесс: - создать временную таблицу - вставить во временную таблицу.

Код для выполнения такой же:

CREATE TABLE #tempTable (Column1 int, Column2 varchar(max));
INSERT INTO #tempTable 
EXEC [app].[Sproc_name]
@param1 = 1,
@param2 =2;
8
27.04.2019 10:48:09

После поиска я нашел способ динамически создавать временную таблицу для любой хранимой процедуры, не используя OPENROWSETили не OPENQUERYиспользуя общую схему определения результата хранимой процедуры, особенно когда вы не являетесь администратором базы данных.

Сервер Sql имеет встроенный sp_describe_first_result_setпроцесс, который может предоставить вам схему любого набора результатов процедур. Я создал таблицу схемы из результатов этой процедуры и вручную установил все поля в NULLABLE.

declare @procname varchar(100) = 'PROCEDURENAME' -- your procedure name
declare @param varchar(max) = '''2019-06-06''' -- your parameters 
declare @execstr nvarchar(max) = N'exec ' + @procname
declare @qry nvarchar(max)

-- Schema table to store the result from sp_describe_first_result_set.
create table #d
(is_hidden  bit  NULL, column_ordinal   int  NULL, name sysname NULL, is_nullable   bit  NULL, system_type_id   int  NULL, system_type_name nvarchar(256) NULL,
max_length  smallint  NULL, precision   tinyint  NULL,  scale   tinyint  NULL,  collation_name  sysname NULL, user_type_id  int NULL, user_type_database    sysname NULL,
user_type_schema    sysname NULL,user_type_name sysname NULL,assembly_qualified_type_name   nvarchar(4000),xml_collection_id    int NULL,xml_collection_database    sysname NULL,
xml_collection_schema   sysname NULL,xml_collection_name    sysname NULL,is_xml_document    bit  NULL,is_case_sensitive bit  NULL,is_fixed_length_clr_type  bit  NULL,
source_server   sysname NULL,source_database    sysname NULL,source_schema  sysname NULL,source_table   sysname NULL,source_column  sysname NULL,is_identity_column bit NULL,
is_part_of_unique_key   bit NULL,is_updateable  bit NULL,is_computed_column bit NULL,is_sparse_column_set   bit NULL,ordinal_in_order_by_list   smallint NULL,
order_by_list_length    smallint NULL,order_by_is_descending    smallint NULL,tds_type_id   int  NULL,tds_length    int  NULL,tds_collation_id  int NULL,
tds_collation_sort_id   tinyint NULL)


-- Get result set definition of your procedure
insert into #d
EXEC sp_describe_first_result_set @exestr, NULL, 0

-- Create a query to generate and populate a global temp table from above results
select 
@qry = 'Create table ##t(' +
stuff(  
    (select ',' + name + ' '+ system_type_name + ' NULL'
    from #d d For XML Path, TYPE)
    .value(N'.[1]', N'nvarchar(max)')
, 1,1,'')
+ ')

insert into ##t 
Exec '+@procname+' ' + @param

Exec sp_executesql @qry

-- Use below global temp table to query the data as you may
select * from ##t

-- **WARNING** Don't forget to drop the global temp table ##t.
--drop table ##t
drop table #d 

Разработано и протестировано на Sql Server версии - Microsoft SQL Server 2016 (окончательная первоначальная версия) - 13.0.1601.5 (сборка 17134 :)

Вы можете настроить схему для используемой версии сервера SQL (при необходимости).

6
13.06.2019 18:26:56

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

Это будет немного запутанным, но оно заимствует из авторов здесь, а также решение Пола Уайта из DBA Stack Exchange Получить типы столбцов результата хранимой процедуры . Опять же, чтобы повторить этот подход и пример не предназначен для процессов в многопользовательской среде. В этом случае определение таблицы задается на короткое время в глобальной временной таблице для ссылки процессом шаблона генерации кода.

Я не полностью проверил это, поэтому могут быть предупреждения, поэтому вы можете перейти по ссылке MSDN в ответе Пола Уайта. Это относится к SQL 2012 и выше.

Сначала используйте хранимую процедуру sp_describe_first_result_set, которая напоминает описание Oracle.

Это позволит оценить первую строку первого набора результатов, поэтому, если ваша хранимая процедура или оператор возвращает несколько запросов, она будет описывать только первый результат.

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

CREATE OR ALTER PROCEDURE [dbo].[sp_GetTableDefinitionFromSqlBatch_DescribeFirstResultSet]
(
     @sql NVARCHAR(4000)
    ,@table_name VARCHAR(100)
    ,@TableDefinition NVARCHAR(MAX) OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @TempTableDefinition NVARCHAR(MAX)
    DECLARE @NewLine NVARCHAR(4) = CHAR(13)+CHAR(10)

    DECLARE @ResultDefinition TABLE (  --The View Definition per MSDN
      is_hidden         bit NOT NULL
    , column_ordinal    int NOT NULL
    , [name]            sysname NULL
    , is_nullable       bit NOT NULL
    , system_type_id    int NOT NULL
    , system_type_name  nvarchar(256) NULL
    , max_length        smallint NOT NULL
    , [precision]       tinyint NOT NULL
    , scale             tinyint NOT NULL
    , collation_name    sysname NULL    
    , user_type_id      int NULL
    , user_type_database    sysname NULL    
    , user_type_schema  sysname NULL
    , user_type_name    sysname NULL    
    , assembly_qualified_type_name      nvarchar(4000)  
    , xml_collection_id         int NULL
    , xml_collection_database   sysname NULL    
    , xml_collection_schema     sysname NULL    
    , xml_collection_name       sysname NULL
    , is_xml_document           bit NOT NULL            
    , is_case_sensitive         bit NOT NULL            
    , is_fixed_length_clr_type  bit NOT NULL    
    , source_server             sysname NULL            
    , source_database           sysname NULL
    , source_schema             sysname NULL
    , source_table              sysname NULL
    , source_column             sysname NULL
    , is_identity_column        bit NULL
    , is_part_of_unique_key     bit NULL
    , is_updateable             bit NULL
    , is_computed_column        bit NULL
    , is_sparse_column_set      bit NULL
    , ordinal_in_order_by_list  smallint NULL   
    , order_by_is_descending    smallint NULL   
    , order_by_list_length      smallint NULL
    , tds_type_id               int NOT NULL
    , tds_length                int NOT NULL
    , tds_collation_id          int NULL
    , tds_collation_sort_id     tinyint NULL
    )

    --Insert the description into table variable    
    INSERT @ResultDefinition
    EXEC sp_describe_first_result_set @sql

    --Now Build the string to create the table via union select statement
    ;WITH STMT AS (
        SELECT N'CREATE TABLE ' + @table_name + N' (' AS TextVal
        UNION ALL

        SELECT 
         CONCAT(
                CASE column_ordinal
                    WHEN 1 THEN '     ' ELSE '   , ' END  --Determines if comma should precede
                , QUOTENAME([name]) , '   ', system_type_name  -- Column Name and SQL TYPE
                ,CASE is_nullable 
                    WHEN 0 THEN '   NOT NULL' ELSE '   NULL' END --NULLABLE CONSTRAINT          
               ) AS TextVal
        FROM @ResultDefinition WHERE is_hidden = 0  -- May not be needed
        UNION ALL

        SELECT N');' + @NewLine
    ) 

    --Now Combine the rows to a single String
    SELECT @TempTableDefinition = COALESCE (@TempTableDefinition + @NewLine + TextVal, TextVal) FROM STMT

    SELECT @TableDefinition = @TempTableDefinition
END

Загадка состоит в том, что вам нужно использовать глобальную таблицу, но вы должны сделать ее достаточно уникальной, чтобы вы могли часто создавать и создавать из нее, не беспокоясь о столкновении.
В примере я использовал Guid (FE264BF5_9C32_438F_8462_8A5DC8DEE49E) для глобальной переменной, заменяя дефисы подчеркиванием

DECLARE @sql NVARCHAR(4000) = N'SELECT @@SERVERNAME as ServerName, GETDATE() AS Today;'
DECLARE @GlobalTempTable VARCHAR(100) = N'##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable'

--@sql can be a stored procedure name like dbo.foo without parameters

DECLARE @TableDef NVARCHAR(MAX)

DROP TABLE IF EXISTS #MyTempTable
DROP TABLE IF EXISTS ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable

EXEC [dbo].[sp_GetTableDefinitionFromSqlBatch_DescribeFirstResultSet] 
    @sql, @GlobalTempTable, @TableDef OUTPUT

--Creates the global table ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable
EXEC sp_executesql @TableDef 

--Now Call the stored procedure, SQL Statement with Params etc.
INSERT ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable
    EXEC sp_executesql @sql 

--Select the results into your undefined Temp Table from the Global Table
SELECT * 
INTO #MyTempTable
FROM ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable

SELECT * FROM #MyTempTable

DROP TABLE IF EXISTS #MyTempTable
DROP TABLE IF EXISTS ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable

Опять же, я протестировал его только с простыми запросами хранимых процедур и простыми запросами, поэтому ваш пробег может отличаться. Надеюсь, это кому-нибудь поможет.

2
23.09.2019 12:33:43

Если вы позволите динамическому SQL создавать временную таблицу, эта таблица будет принадлежать соединению Dynamic SQL, а не соединению, из которого вызывается ваша хранимая процедура.

DECLARE @COMMA_SEPARATED_KEYS varchar(MAX);
DROP TABLE IF EXISTS KV;
CREATE TABLE KV (id_person int, mykey varchar(30), myvalue int);
INSERT INTO KV VALUES
(1, 'age', 16),
(1, 'weight', 63),
(1, 'height', 175),
(2, 'age', 26),
(2, 'weight', 83),
(2, 'height', 185);
WITH cte(mykey) AS (
    SELECT DISTINCT mykey FROM KV
) 
SELECT @COMMA_SEPARATED_KEYS=STRING_AGG(mykey,',') FROM cte;
SELECT @COMMA_SEPARATED_KEYS AS keys;

введите описание изображения здесь

DECLARE @ExecuteExpression varchar(MAX);

DROP TABLE IF EXISTS #Pivoted;

SET @ExecuteExpression = N'
SELECT * 
INTO #Pivoted
FROM
(
    SELECT
        mykey,
        myvalue,
        id_person
    FROM KV
) AS t
PIVOT(
    MAX(t.myvalue) 
    FOR mykey IN (COMMA_SEPARATED_KEYS)
) AS pivot_table;
';

SET @ExecuteExpression = REPLACE(@ExecuteExpression, 'COMMA_SEPARATED_KEYS', @COMMA_SEPARATED_KEYS);

EXEC(@ExecuteExpression);

SELECT * FROM #Pivoted;

Сообщение 208, уровень 16, состояние 0 Неверное имя объекта "#Pivoted". Это потому, что #Pivoted принадлежит подключению Dynamic SQL. Итак, последняя инструкция

SELECT * FROM #Pivoted

выходит из строя.

Один из способов избежать этой проблемы - убедиться, что все ссылки на #Pivoted сделаны из самого динамического запроса:

DECLARE @COMMA_SEPARATED_KEYS varchar(MAX);
DROP TABLE IF EXISTS KV;
CREATE TABLE KV (id_person int, mykey varchar(30), myvalue int);
INSERT INTO KV VALUES
(1, 'age', 16),
(1, 'weight', 63),
(1, 'height', 175),
(2, 'age', 26),
(2, 'weight', 83),
(2, 'height', 185);
WITH cte(mykey) AS (
    SELECT DISTINCT mykey FROM KV
) 
SELECT @COMMA_SEPARATED_KEYS=STRING_AGG(mykey,',') FROM cte;
SELECT @COMMA_SEPARATED_KEYS AS keys;


DECLARE @ExecuteExpression varchar(MAX);

DROP TABLE IF EXISTS #Pivoted;

SET @ExecuteExpression = N'
SELECT * 
INTO #Pivoted
FROM
(
    SELECT
        mykey,
        myvalue,
        id_person
    FROM KV
) AS t
PIVOT(
    MAX(t.myvalue) 
    FOR mykey IN (COMMA_SEPARATED_KEYS)
) AS pivot_table;
SELECT * FROM #Pivoted;
';

SET @ExecuteExpression = REPLACE(@ExecuteExpression, 'COMMA_SEPARATED_KEYS', @COMMA_SEPARATED_KEYS);

EXEC(@ExecuteExpression);

введите описание изображения здесь

0
29.11.2019 16:09:17