Как сохранить результат хранимой процедуры в таблице? — Microsoft SQL Server

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

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

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

Для начала давайте создадим тестовую хранимую процедуру, которая будет возвращать табличные данные. Все действия ниже я буду выполнять на Microsoft SQL Server 2016 Express, на текущий момент вышла уже 2017 версия SQL Server, о том, что нового в ней появилось, можете почитать в материале – «Обзор основных нововведений в Microsoft SQL Server 2017».

Создание хранимой процедуры, которая возвращает табличные данные

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

   
   CREATE PROCEDURE sp_Test
   AS
   SELECT * FROM (VALUES (1, 'Компьютер', 500),
                                          (2, 'Принтер', 300),
                                          (3, 'Монитор', 300)
                           ) AS TmpTable (ProductId, ProductName, Summa)
   GO

   EXEC sp_Test

Как видите, процедура создана и возвращает табличные данные.

Заметка! Как сформировать и вставить в таблицу заданное количество строк на T-SQL.

Способ 1 – Используем вызов процедуры в конструкции INSERT INTO

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

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

   
   --Создаем временную таблицу
   CREATE TABLE #TmpTable (ProductId INT, ProductName VARCHAR (30), Summa MONEY);

   --Осуществляем вставку
   INSERT INTO #TmpTable
   EXEC sp_Test

   --Проверяем, т.е. делаем выборку из таблицы
   SELECT * FROM #TmpTable

   --Удаляем временную таблицу
   DROP TABLE #TmpTable

Способ 2 – Используем связанный сервер

В данном случае мы создаем связанный сервер, который будет ссылаться на самого себя, иными словами, на текущий сервер. Затем с помощью конструкции OPENQUERY мы обращаемся к связанному серверу, запуская на нем соответствующую процедуру. Результат в данном случае мы можем сохранять уже с помощью конструкции SELECT INTO в новую таблицу (в нашем случае для примера во временную таблицу).

   
   --Создаем связанный сервер   
   EXEC sp_addlinkedserver @server = N'CurrentServer',
                                                @srvproduct = N'',
                                                @provider = N'SQLOLEDB', 
                                                @datasrc = N'TESTSERVER\SQLEXPRESS'
   GO
   --Выполняем запрос с сохранением данных во временную таблицу
   SELECT * INTO #TmpTable FROM OPENQUERY(CurrentServer, 
                                          'SET FMTONLY OFF EXEC TestBase.dbo.sp_Test')
   --Проверяем полученные данные
   SELECT * FROM #TmpTable
   --Удаляем временную таблицу
   DROP TABLE #TmpTable

Плюс данного способа в том, что Вам уже не нужно заранее создавать таблицу и соответственно знать количество столбцов. Но, как Вы понимаете, у данного способа есть и недостатки, например, Вы должны для выполнения таких процедур предварительно создать связанный сервер, также данный способ не будет работать, если табличные данные не имеют названия колонок (например, SELECT 1, 2, 3) и если в хранимой процедуре используются временные таблицы.

Способ 3 – Используем конструкцию OPENROWSET

Этот способ подразумевает использование функции OPENROWSET и поставщика OLE DB. Для использования данного способа у Вас должен быть включен параметр Ad Hoc Distributed Queries.

   
   --Включаем параметр Ad Hoc Distributed Queries
   EXEC sp_configure 'show advanced options', 1
   RECONFIGURE
   EXEC sp_configure 'Ad Hoc Distributed Queries', 1
   RECONFIGURE
   GO
   --Выполняем запрос с сохранением данных во временную таблицу
   SELECT * INTO #TmpTable FROM OPENROWSET('SQLOLEDB',
                                           'Server=TESTSERVER\SQLEXPRESS;Trusted_Connection=Yes;', 
                                           'SET FMTONLY OFF EXEC TestBase.dbo.sp_Test')
   --Проверяем полученные данные
   SELECT * FROM #TmpTable
   --Удаляем временную таблицу
   DROP TABLE #TmpTable

Плюс этого способа в том, что Вам уже не нужно предварительно создавать ни таблицу, ни связанный сервер. Но, минусы все равное есть, конструкцию использовать не получится, если в хранимой процедуре используются временные таблицы или есть неименованные столбцы, и, как я уже сказал, предварительно нужно включить параметр «Ad Hoc Distributed Queries».

Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.

У меня все, надеюсь, материал был Вам полезен, пока!