Возвращение значения из EXEC(@sql) в переменную в SQL

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

Чтобы извлечь данные из динамического SQL, примените выводные параметры вместе с sp_executesql. Сначала объявите переменную, затем включите её в запрос, отметив как OUTPUT:

DECLARE @Result NVARCHAR(100);
EXEC sp_executesql N'SELECT @Result = MyColumn FROM MyTable WHERE ...',
                   N'@Result NVARCHAR(100) OUTPUT', 
                   @Result OUTPUT;
SELECT @Result;

Извлечение сложных данных при помощи переменных таблицы

Если предполагается получение нескольких строк или колонок, идеально подойдут переменные таблицы. Объявите переменную таблицы и заполните её результатами выполнения динамического SQL:

DECLARE @ResultsTable TABLE (Column1 INT, Column2 NVARCHAR(100));
INSERT INTO @ResultsTable
EXEC sp_executesql N'SELECT Column1, Column2 FROM MyTable WHERE ...';
SELECT * FROM @ResultsTable;

Прямое присвоение значений через SELECT INTO

Если вам требуется только одна строка, обратите внимание на SELECT INTO, который позволяет прямо в динамическом SQL присвоить значения:

DECLARE @Result NVARCHAR(100);
EXEC(@sql + 'SELECT @Result = Column1 INTO #TempTable FROM MyTable WHERE ...');
SELECT @Result = Column1 FROM #TempTable;
DROP TABLE #TempTable;

Подавление лишних сообщений с SET NOCOUNT ON

Чтобы избежать вывода лишних сообщений о количестве затронутых строк, используйте SET NOCOUNT ON:

SET NOCOUNT ON;
-- Ваш код динамического SQL
SET NOCOUNT OFF;

Контроль успешности выполнения с помощью @@ROWCOUNT и @@ERROR

Будьте уверены в успешности выполнения операций, проверяя их состояние с помощью @@ROWCOUNT и @@ERROR:

DECLARE @RowCount INT, @Error INT;
-- После выполнения динамического SQL
SET @RowCount = @@ROWCOUNT;
SET @Error = @@ERROR;

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

Запустите и получите значение из exec(@sql), словно вы отправились в космическое приключение:

🔄 Выполнение динамического SQL (🏷️ @sql)

Отправляемся в путешествие по SQL Галактике:

SET @result = 0;
EXEC sp_executesql @sql, N'@Result INT OUTPUT', @Result OUTPUT;

🔍 Развертываем сеть в глубинах космоса

Получаем нашу награду (🍾):

🍾 --> [🪐 Возвращаемое значение]

Анализируем собранные данные:

⚓️ Успех! Получены данные: **@result**

Миссия успешно завершена!

Подготовка к работе с разными типами данных

Будьте готовы работать с данными различных типов:

DECLARE @IntResult INT, @DateResult DATETIME;
-- Проверка типов данных для соответствия ожиданиям

Очистка рабочего пространства

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

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL 
    DROP TABLE #TempTable;  -- Очищаем пространство.

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

Использование кодов возврата

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

IF @Error != 0
    BEGIN
        RETURN @Error;  -- Сигнализируем о проблеме.
    END
ELSE
    BEGIN
        RETURN 0;  -- Все прошло успешно.
    END

Использование полученных данных

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

-- применяем данные из @ResultsTable
UPDATE AnotherTable
SET AnotherTable.Column1 = r.Column1
FROM @ResultsTable r
WHERE AnotherTable.ID = r.ColumnID;  -- Расширяем границы известного.

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

1. EXECUTE (Transact-SQL) – SQL Server | Microsoft Discover — официальная документация по команде EXECUTE в T-SQL.
2. The Keys to Dynamic SQL and Stored Procedures – Stack Overflow — обширный набор рекомендаций для работы с динамическим SQL от сообщества разработчиков.
3. Decoding OUT Params in SQL Server Stored Procedures – SQL Shack — руководство по использованию параметров OUTPUT.
4. Runtime Dance: Dynamic SQL in Stored Procedures – Database Guru — практические советы по применению динамического SQL в хранимых процедурах.