Табличные переменные в динамическом 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