SQL-Ex blog

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

Для демонстрации я создал таблицу Products, которая будет использоваться в последующих примерах. Структура таблицы Products такова:

CREATE TABLE Products (

	Id INT NOT NULL,

	Name NVARCHAR(100) NULL,

	Description NVARCHAR(200) NULL,

	CreatedDate DATETIME2 NOT NULL CONSTRAINT [DF_Products_CreatedDate] DEFAULT GETUTCDATE(),

	CreatedBy NVARCHAR(150) NOT NULL,

	CONSTRAINT PK_Product PRIMARY KEY(Id)

 );

Вставка отдельного продукта

Рассмотрим сценарий, когда пользователь получает доступ к вашему приложению, и ему требуется зарегистрировать единственный продукт в этом приложении. Для этого вам необходимо иметь процедуру для добавления продукта в базу данных. Итак вам требуется создать процедуру для добавления отдельного продукта в таблицу Products. Эта процедура должна получить в качестве параметров Id, Name, Description и пользователя, который создал продукт:

CREATE PROCEDURE InsertProduct (  

		@Id INT,  

		@Name NVARCHAR(100),  

		@Description NVARCHAR(200),  

		@User NVARCHAR(150)  

	)  

AS   

BEGIN

	INSERT INTO Products (

		Id,

		Name,

		Description,

		CreatedBy

	) 

	VALUES (	

		  @Id,  

		  @Name,  

		  @Description,  

		  @User

	);

END

Для тестирования процедуры мы можем выполнить несколько скриптов, добавив BEGIN TRANSACTION с ROLLBACK в конце (это полезно при тестировании, чтобы не пришлось удалять/изменять/возвращать данные при каждом тесте), для вставки продуктов:

BEGIN TRANSACTION

	SELECT * FROM Products;

	EXEC InsertProduct 1, 'Galaxy S22', 'Smartphone Samsung Galaxy S22', 'Henrique';

	EXEC InsertProduct 2, 'iPhone 14 Pro', 'Smartphone Apple iPhone 14 Pro', 'Henrique';

	EXEC InsertProduct 3, 'iPhone 13 Pro', 'Smartphone Apple iPhone 13 Pro', 'Henrique';

	SELECT * FROM Products;

ROLLBACK

• На первой строке находится оператор BEGIN TRANSACTION, который позволит нам отменить изменения в конце выполнения.


• На второй строке мы выполняем запрос SELECT для проверки данных в таблице Products.


• На 3 - 5 строках мы выполняем процедуру InsertProduct для вставки продуктов. Обратите внимание, что для того, чтобы вставить 3 продукта, нам необходимо выполнить процедуру три раза, по разу на каждый продукт.


• В строке 6 мы опять выполняем запрос SELECT, чтобы проверить данные в таблице Products.


• В последней строке выполняется оператор ROLLBACK для отмены сделанных изменений.

Вот результат:

Вставка множества продуктов

Теперь рассмотрим сценарий, когда вместо добавления единственного продукта, вы получаете список продуктов, которые требуется добавить в таблицу Products. В этом случае процедура должна содержать параметр табличного типа (который будет работать как массив продуктов).

Табличный тип должен содержать столбцы с теми же свойствами, что и столбцы в таблице Products. Для данного примера это Id, Name и Description:

CREATE TYPE ProductType AS TABLE (

	Id INT NOT NULL,

	Name NVARCHAR(100) NULL,

	Description NVARCHAR(200) NULL,

	PRIMARY KEY(Id)

);

После создания типа таблицы, его можно увидеть здесь:



Давайте создадим новую процедуру с именем InsertProducts, эта процедура будет иметь два параметра: таблица типа и пользователь, который добавляет записи. Вот эта процедура:

CREATE PROCEDURE InsertProducts (  

		@Products ProductType READONLY,

		@User NVARCHAR(150)

	)  

AS   

BEGIN

	INSERT INTO Products (

		Id,

		Name,

		Description,

		CreatedBy

	) 

	SELECT	

		prd.Id,

		prd.Name,

		prd.Description,

		@User

	FROM @Products prd

END

• В строке 2 находится параметр @Products типа ProductType, и он должен иметь ключевое слово READONLY.


• В строке 3 находится параметр @CreatedBy типа NVARCHAR, который используется для имени пользователя, который выполняет процедуру для вставки продуктов. Замечание: этот второй параметр используется лишь для демонстрации того, что даже при наличии параметра табличного типа возможно использование и других параметров различных типов. В нашем случае для получения пользователя, который выполнял скрипт SQL, вы можете использовать SYSTEM_USER непосредственно в скрипте SQL вместо передачи пользователя через параметр.


• В строке 7 начинается оператор INSERT.


• В строке 13 находится запрос SELECT, который будет читать данные из табличного типа, который передается как параметр (@Products), и будет использовать эти данные для вставки в таблицу Products.

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

BEGIN TRANSACTION

	SELECT * FROM Products;

	DECLARE @Products ProductType;

	INSERT INTO @Products

	SELECT 1, 'Galaxy S22+', 'Smartphone Samsung Galaxy S22+'

	UNION ALL

	SELECT 2, 'iPhone 14 Pro', 'Smartphone Apple iPhone 14 Pro'

	UNION ALL

	SELECT 3, 'iPhone 13 Pro', 'Smartphone Apple iPhone 13 Pro';

	EXEC InsertProducts @Products, 'Henrique';

	SELECT * FROM Products;

ROLLBACK

• В строке 1 стартует новая транзакция.


• В строке 2 мы первый раз выполняем оператор SELECT для проверки данных в таблице Products перед выполнением процедуры.


• В строке 3 объявляется переменная типа ProductType.


• В строках 4 - 6 в переменную @products вставляется три записи.


• В строке 7 выполняется процедура вставки продуктов, получая в качестве параметров переменную табличного типа (@Products) и пользователя ('Henrique').


• В строке 8 снова выполняется оператор SELECT к таблице Products, и мы ожидаем, что три строки были вставлены в эту таблицу.


• В строке 9 выполняется откат транзакции для отмены изменений.

Вот результат:



Первый запрос SELECT не вернул никаких данных (как и ожидалось, поскольку это новая таблица). Второй запрос SELECT показывает добавление трех продуктов в таблицу Products (что ожидалось после выполнения процедуры вставки).

Давайте выполним еще один тест для случая, когда в таблице Products имеются данные. Для этого давайте вставим некоторые данные в таблицу:

INSERT INTO Products (Id, Name, Description, CreatedBy)

	VALUES (1, 'Galaxy S21+', 'Smartphone Samsung Galaxy S21+', 'Henrique'),

	        (2, 'Galaxy S22', 'Smartphone Samsung Galaxy S22', 'Henrique'),

		(3, 'Galaxy S22+', 'Smartphone Samsung Galaxy S22+', 'Henrique');

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

BEGIN TRANSACTION

	SELECT * FROM Products;

	DECLARE @Products ProductType;

	INSERT INTO @Products

	SELECT 4, 'iPhone 13 Pro', 'Smartphone Apple iPhone 13 Pro'

	UNION ALL

	SELECT 5, 'iPhone 14 Pro', 'Smartphone Apple iPhone 14 Pro';

	EXEC InsertProducts @Products, 'Henrique';

	SELECT * FROM Products;

ROLLBACK

Вот результат:



Как и ожидалось, новые записи с id 4 и 5 добавились в таблицу Products.

Заключение

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

Вот ссылка на репозиторий скриптов на GitHub: https://github.com/henriquesd/SQLExamples

Ссылки по теме
1. Хранимые процедуры SQL: входные и выходные параметры, типы, обработка ошибок и кое-что еще
2. Как использовать функциональность массивов в SQL Server?
3. Хранимые процедуры или sp_executesql в SQL Server: что лучше?