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

Создание типа таблицы

Первый шаг в использовании параметров с таблицами – создание типа таблицы, который определяет структуру таблицы. Это можно сделать с помощью оператора CREATE TYPE. Например, давайте создадим тип таблицы с именем DeptType с колонками DeptId и DeptName:

CREATE TYPE DeptType AS TABLE (
    DeptId INT,
    DeptName VARCHAR(30)
);

Объявление хранимой процедуры

Затем нам нужно объявить хранимую процедуру, у которой есть параметр типа таблицы, который мы только что создали. Это можно сделать с помощью оператора CREATE PROCEDURE. Например, давайте создадим хранимую процедуру с именем InsertDepartment с параметром @InsertDept_TVP типа DeptType:

CREATE PROCEDURE InsertDepartment
    @InsertDept_TVP DeptType READONLY
AS
    INSERT INTO Department (DepartmentID, DepartmentName)
    SELECT * FROM @InsertDept_TVP;

Использование параметров с таблицами

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

DECLARE @DepartmentTVP AS DeptType;

Затем мы можем использовать оператор INSERT, чтобы заполнить переменную типа таблицы данными:

INSERT INTO @DepartmentTVP (DeptId, DeptName)
VALUES (1, 'Accounts'), (2, 'Purchase'), (3, 'Software'), (4, 'Stores'), (5, 'Marketing');

Наконец, мы можем передать переменную типа таблицы в хранимую процедуру и выполнить ее:

EXEC InsertDepartment @DepartmentTVP;

Важные моменты, которые следует запомнить

Есть несколько важных моментов, которые следует учитывать при работе с параметрами с таблицами:

• Параметры с таблицами должны передаваться как параметры READONLY в SQL-процедуры. Операции DML, такие как UPDATE, DELETE или INSERT, не могут выполняться с параметром с таблицей внутри тела процедуры.
• Параметр с таблицей не может использоваться в качестве цели оператора SELECT INTO или INSERT EXEC. Однако его можно использовать в разделе FROM оператора SELECT INTO или в строке INSERT EXEC или хранимой процедуре.

Вывод

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