Як зберегти результат процедури, що в таблиці? - Microsoft SQL Server

1. Створення збереженої процедури, яка повертає табличні дані
2. Спосіб 1 - Використовуємо виклик процедури в конструкції INSERT INTO
3. Спосіб 2 - Використовуємо пов'язаний сервер
4. Спосіб 3 - Використовуємо конструкцію OPENROWSET

Сьогодні в матеріалі ми з Вами розглянемо кілька способів реалізації того, як можна в 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

Як бачите, процедура створена і повертає табличні дані.

Спосіб 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».

Замітка! Якщо Вас цікавить мова T-SQL, то рекомендую почитати мою книгу « Шлях програміста T-SQL », В ній я детально, з великою кількістю прикладів, розповідаю практично про всі можливості мови Transact-SQL.

У мене все, сподіваюся, матеріал був Вам корисний, поки!