Выполнение динамических T-SQL инструкций в Microsoft SQL Server

В данном материале мы поговорим о выполнении динамического T-SQL кода, Вы узнаете, как сформировать текстовую строку, содержащую SQL инструкцию, и запустить ее на выполнение в Microsoft SQL Server.

Динамический код в Microsoft SQL Server

Динамический код в Microsoft SQL Server

Microsoft SQL Server позволяет выполнять SQL инструкции, сформированные динамически, так как иногда без этого просто не обойтись. Например, для того чтобы динамически выполнять инструкции, которые с первого взгляда кажутся статическими. К примеру, оператор PIVOT, его синтаксис, предполагает вручную перечислять выходные столбцы, количество и название которых заранее нам могут быть просто не известны. Но мы можем сформировать динамическую инструкцию, которая будет автоматически узнавать и подставлять все необходимые нам значения в SQL запрос, и тем самым нам уже не нужно знать и тем более вручную указывать выходные столбцы, в случае с оператором PIVOT, кстати, ранее я уже приводил пример реализации динамического PIVOT.

Динамическая SQL инструкция – это просто текстовая строка, которая после преобразования и подставки всех значений, исполняется SQL сервером как обычная SQL инструкция.

Таким образом, чтобы сформировать динамическую SQL инструкцию, необходимо просто сформировать текстовую строку с указанием необходимых переменных, значения которых Вы хотите подставлять, или произвести конкатенацию строк с переменными, используя оператор + (плюс).

В Microsoft SQL Server существует два способа запускать на выполнения строки, содержащие SQL инструкции, это: команда EXECUTE и системная хранимая процедура sp_executesql.

Исходные данные для примеров

Перед тем как переходить к рассмотрению примеров выполнения динамических инструкций, давайте создадим тестовые данные, например, таблицу TestTable, и добавим в нее несколько строк.

    
   --Создание таблицы
    CREATE TABLE TestTable(
            [ProductId]   [INT] IDENTITY(1,1) NOT NULL,
            [CategoryId]  [INT] NOT NULL,
            [ProductName] [VARCHAR](100) NOT NULL,
            [Price]       [Money] NULL
   )
   GO
   --Вставляем в таблицу данные
   INSERT INTO TestTable
     VALUES (1, 'Клавиатура', 100),
            (1, 'Мышь', 50),
            (2, 'Системный блок', 200)
   GO
   --Выборка данных
   SELECT * FROM TestTable

Скриншот 1

Команда EXECUTE в T-SQL

EXECUTE (сокращенно EXEC) – команда для запуска хранимых процедур и SQL инструкций в виде текстовых строк.

Перед тем как переходить к примерам, следует отметить, что использование динамического кода с использованием команды EXEC – это не безопасно! Дело в том, что для того чтобы сформировать динамическую SQL инструкцию, необходимо использовать переменные для динамически изменяющихся значений. Так вот, если эти значения будут приходить от клиентского приложения, т.е. от пользователя, злоумышленники могут передать и, соответственно, внедрить в нашу инструкцию вредоносный код в виде текста, а мы его просто исполним в БД, думая, что нам передали обычные параметры. Поэтому все такие значения следует очень хорошо проверять, перед тем как подставлять в инструкцию.

Пример использования EXEC в T-SQL

Сейчас мы с Вами сформируем динамический SQL запрос, текст которого мы сохраним в переменной, и затем выполним его с помощью команды EXEC.

Текст запроса будет храниться в переменной @SQL_QUERY, в переменной @Var1 будет храниться значение, которое мы будем подставлять в наш запрос, для того чтобы этот запрос стал динамическим (в нашем случае мы вручную присвоим статическое значение в переменную, хотя это значение можно узнавать, например, с помощью запроса или каких-то вычислений).

Для формирования строки мы будет использовать конкатенацию строк, а именно оператор + (плюс), только стоит понимать, что в этом случае выражения, участвующие в операции, должны иметь текстовый тип данных. Переменная @Var1 у нас будет иметь тип данных INT, поэтому, чтобы соединить ее со строкой, мы предварительно преобразуем ее значение к типу данных VARCHAR.

Для наглядности того, какой именно SQL запрос у нас получился, мы просто посмотрим, что у нас хранится в переменной @SQL_QUERY инструкцией SELECT.

   
   --Объявляем переменные
   DECLARE @SQL_QUERY VARCHAR(200),
   @Var1 INT;

   --Присваиваем значение переменным
   SET @Var1 = 1;

   --Формируем SQL инструкцию
   SET @SQL_QUERY = 'SELECT * FROM TestTable WHERE ProductID = ' + CAST(@Var1 AS VARCHAR(10));

   --Смотрим на итоговую строку
   SELECT @SQL_QUERY AS [TEXT QUERY]

   --Выполняем текстовую строку как SQL инструкцию
   EXEC (@SQL_QUERY)

Скриншот 2

Хранимая процедура sp_executesql в T-SQL

sp_executesql – это системная хранимая процедура Microsoft SQL Server, которая выполняет SQL инструкции. Эти инструкции могут содержать параметры, тем самым делая их динамическими.

Процедура sp_executesql имеет несколько параметров, первым параметром указывается текст SQL инструкции, вторым объявляются переменные, третий и все последующие — это передача значений для переменных в процедуру и, соответственно, подстановка в нашу инструкцию.

Все параметры процедуры sp_executesql необходимо передавать в формате Unicode (тип данных строк должен быть NVARCHAR).

Пример использования sp_executesql в T-SQL

В этом примере итоговый результат у нас будет точно таким же, как и в примере с EXEC, только динамические значения, у нас это переменная @Var1, мы объявим и передадим в виде параметров хранимой процедуры sp_executesql.

   
   --Объявляем переменные
   DECLARE @SQL_QUERY NVARCHAR(200);

   --Формируем SQL инструкцию
   SELECT @SQL_QUERY = N'SELECT * FROM TestTable WHERE ProductID = @Var1;';

   --Смотрим на итоговую строку
   SELECT @SQL_QUERY AS [TEXT QUERY]

   --Выполняем текстовую строку как SQL инструкцию
   EXEC sp_executesql @SQL_QUERY,--Текст SQL инструкции
                      N'@Var1 AS INT', --Объявление переменной @Var1
                      @Var1 = 1 --Передаем значение для переменной @Var1

Скриншот 3

У меня на этом все, надеюсь, материал был Вам интересен и полезен, если Вас интересуют другие возможности языка T-SQL, то рекомендую почитать мою книгу «Путь программиста T-SQL», в ней я подробно рассказываю про все конструкции языка Transact-SQL, пока!

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.