08 октября 2020

Табличные переменные в динамическом SQL

    Табличные переменные являются одной из интересных возможностей MS SQL Server. Эта удобная альтернатива временным таблицам, которую можно использовать для хранения небольших наборов данных в виде строк таблицы. Сегодня мне впервые потребовалось использовать их совместно с динамическим SQL.
    Мой триггер должен был взять текст запроса из настроечной таблицы, выполнить его с определенными параметрами, результирующий набор строк сохранить в табличную переменную, а ее отдать на съедение курсору. Попытка подсунуть sp_executesql табличную переменную в качестве OUTPUT-параметра потерпела неудачу. Но решение вопроса оказалось очень простым.
    Создадим тестовую таблицу:
CREATE TABLE Test(ID int, NAME varchar(50));

INSERT INTO Test (ID, NAME) 
VALUES (1, 'Test 1'), (2, 'Test 2'), (3, 'Test 3'), (4, 'Test 4');
В скрипте поставим INSERT INTO перед вызовом sp_executesql:
DECLARE @sql nvarchar(4000) = N'SELECT ID, NAME FROM Test WHERE ID<=@MaxID'
DECLARE @tv table(ID int, NAME varchar(50))
INSERT INTO @tv EXEC sp_executesql @sql, N'@MaxID int', @MaxID = 3

SELECT * FROM @tv
Задача выполнена. Дополню эту заметку еще информацией о том, как в динамическом SQL прочитать данные из табличной переменной. Для этого сначала создадим свой тип:
CREATE TYPE tt AS TABLE (ID int, NAME varchar(50))
а потом используем его для передачи табличной переменной в sp_executesql:
DECLARE @sql nvarchar(4000) = N'SELECT ID, NAME FROM Test WHERE ID<=@MaxID'
DECLARE @tv tt
INSERT INTO @tv EXEC sp_executesql @sql, N'@MaxID int', @MaxID = 3

EXEC sp_executesql N'SELECT * FROM @t', N'@t tt ReadOnly', @t = @tv

2 комментария:

  1. Как обновить табличную переменную в динамическом sql, через создание типа пройдет?

    ОтветитьУдалить
  2. Если я не ошибаюсь, то табличная переменная в sp_executesql передается только ReadOnly. Поэтому ее можно обновлять только прямо в скрипте:
    UPDATE @tv SET name = '333' WHERE id = 3

    ОтветитьУдалить