SQL: Передача таблицы в хранимую процедуру

Введение

SQL Server 2005 и более ранние версии не поддерживают передачу табличных переменных в хранимую процедуру.

Рассмотрим в этом посте функционал SQL Server 2008 и более поздних версий, позволяющий передавать таблицу в хранимую процедуру или функцию.

Код

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

Итак, первый этап – создание пользовательского табличного типа. Ниже в коде TSQL создается пользовательский табличный тип ItemInfo:

CREATE TYPE ItemInfo AS TABLE
(
 ItemId VARCHAR(50),
 Qty INT
)

Для просмотра созданного типа можно использовать системное представление SYS.TYPE. Запрос ниже возвращает все определенные в системе типы:

SELECT * FROM SYS.TYPES

Для вывода данных только по табличным типам этот запрос используется следующим образом:

SELECT * FROM SYS.TYPES WHERE is_table_type = 1

Еще данные по табличным типам можно получить таким способом:

SELECT * FROM SYS.TABLE_TYPES

Структуру самой табличной переменной можно посмотреть следующим образом:

SELECT * 
FROM SYS.COLUMNS c
JOIN SYS.TABLE_TYPES tt ON c.[object_id] = tt.type_table_object_id 
WHERE tt.name = 'ItemInfo'

Итак, у нас есть необходимый табличный тип. Теперь создадим переменную с типом ItemInfo и попробуем вставить в нее несколько записей. Затем запросим данные из переменной для проверки корректности вставки:

DECLARE @items AS ItemInfo

INSERT INTO @items (ItemId, Qty)
	SELECT 'A0000001', 10 UNION ALL
	SELECT 'B0000001', 20 UNION ALL
	SELECT 'C0000001', 30

SELECT * FROM @items

Результат:

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

CREATE PROCEDURE sp_SelectItemInfo
(
	@Items	ItemInfo
)

AS

SELECT * FROM @Items

Упс, получили ошибку:

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

CREATE PROCEDURE sp_SelectItemInfo
(
	@Items	ItemInfo READONLY
)

AS

SELECT * FROM @Items

Теперь выполним созданную хранимую процедуру - запустим следующий код:

DECLARE @Items AS ItemInfo

INSERT INTO @items (ItemId, Qty)
	SELECT 'A0000010', 100 UNION ALL
	SELECT 'B0000010', 200 UNION ALL
	SELECT 'C0000010', 300

EXECUTE sp_SelectItemInfo @Items

Результат:

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

CREATE PROCEDURE sp_SelectItemInfo1
(
	@Items	ItemInfo READONLY
)

AS

SELECT * FROM @Items

INSERT INTO @Items(ItemId, Qty)
	SELECT 'D0000010', 400

Ошибка:

Заключение

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