Как получить результат динамического запроса SQL в переменную

Быстрый ответ

Для того, чтобы сохранить результат выполнения динамического SQL в переменную, вам поможет процедура sp_executesql. Она принимает необходимую строку запроса и параметры, которые могут быть указаны как OUTPUT:

DECLARE @SQL NVARCHAR(MAX), @Result INT, @ParamDef NVARCHAR(MAX);
SET @SQL = N'SELECT @Res = COUNT(*) FROM MyTable WHERE MyColumn = @Value;';
SET @ParamDef = N'@Res INT OUTPUT, @Value INT';
EXEC sp_executesql @SQL, @ParamDef, @Res = @Result OUTPUT, @Value = 10;
SELECT @Result; -- И вот результат. Ничего сложного!

Замените на MyTable, MyColumn и @Value нужные вам значения. После выполнения переменная @Result будет содержать ожидаемый результат.

Обработка SQL инъекций

Передача параметров с валидацией типа

Параметризованный динамический SQL призван защитить вас от атак через SQL инъекции:

DECLARE @SQL NVARCHAR(MAX), @FieldName NVARCHAR(128), @TableName NVARCHAR(128), @Result INT;
SET @FieldName = N'MyColumn';
SET @TableName = N'MyTable';
SET @SQL = N'SELECT @Res = COUNT(*) FROM ' + QUOTENAME(@TableName) + ' WHERE ' + QUOTENAME(@FieldName) + ' = @Value';
EXEC sp_executesql @SQL, N'@Res INT OUTPUT, @Value INT', @Res = @Result OUTPUT, @Value = 10;
SELECT @Result;

Подробнее об этом расскажет наш спикер на видео

Важность типов данных в динамическом SQL

Важно тщательно обрабатывать типы данных в SQL и параметрах sp_executesql. Несовпадение типов может привести к ошибкам в исполнении.

Обработка ошибок

Не всегда все идет по плану, ошибки случаются. Для их обработки применяется конструкция TRY/CATCH:

BEGIN TRY
    -- Здесь исполняем динамический SQL
END TRY
BEGIN CATCH
    -- Пойманная ошибка будет обработана так, как требует ситуация.
    SELECT ERROR_MESSAGE();
END CATCH

Оптимизация

Избегайте затруднений в ходе выполнения запроса. Для этого применяйте планы выполнения для анализа работы ваших запросов.

Визуализация

Динамический SQL 📡   Переменная 📻

DECLARE @Result INT;
EXEC sp_executesql N'SELECT @InnerResult = COUNT(*) FROM MyTable;', 
                   N'@InnerResult INT OUTPUT', 
                   @Result OUTPUT;
-- Сигнал усилен и принят успешно

Настройте вашу SQL-антенну (@Result) таким образом, чтобы уловить результат динамического запроса!

Анализ динамического SQL

Использование EXECUTE AS

EXECUTE AS в SQL позволяет временно выполнить действие от имени другого пользователя. Это может быть полезным при необходимости доступа к ресурсам, для которых требуются особые привилегии:

EXECUTE AS LOGIN = 'YourLogin';
-- Именно так работает волшебство SQL!
REVERT;

Обработка значений NULL

Значения NULL могут оказаться сложными для обработки. Для этого применяйте функции ISNULL или COALESCE:

SET @SQL = N'SELECT @Res = ISNULL(SUM(MyColumn), 0) FROM MyTable WHERE MyCondition = @Value';

Множественные результаты

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

DECLARE @TempTable TABLE (Column1 INT, Column2 VARCHAR(100));
INSERT INTO @TempTable EXEC sp_executesql @SQL;

Поддержание порядка в коде

Важно сохранять порядок в коде. Для этого рекомендуется хранить динамический SQL внутри хранимых процедур.

Полезные материалы

1. Выполнение динамических SQL-команд в SQL Server – Здесь вы найдете подробное описание методов выполнения динамических SQL-команд и способов сохранения результатов в переменные.