Выполнение динамических T-SQL инструкций в Microsoft SQL Server | – IT-блог для начинающих

Working with the sql server command line (sqlcmd)

Ввод запроса

Начните вводить оператор SQL в приглашении 1>. Вы можете использовать столько строк, сколько хотите для своего запроса, нажимая клавишу Enter после каждой строки. SQL Server не выполняет ваш запрос, пока не получит явную инструкцию.

 SELECT * 
FROM HumanResources.shift 

Выполнение запроса

Когда вы будете готовы выполнить ваш запрос, введите команду GO в новой командной строке в SQLCMD и нажмите Enter . SQLCMD выполнит ваш запрос и отобразит результаты на экране.

Выход из sqlcmd

Когда вы будете готовы выйти из SQLCMD, введите команду EXIT в пустой командной строке, чтобы вернуться в командную строку Windows.

Динамический код в 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

Команда execute в t-sql

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

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

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

Открытие командной строки

Чтобы запустить SQLCMD, вы должны сначала открыть утилиту командной строки Windows. В Windows XP нажмите Пуск > Запустить , а затем введите CMD в текстовом поле, а затем нажмите ОК . В Windows Vista нажмите кнопку Windows , введите CMD в поле Поиск и нажмите Enter .

Вы должны увидеть командную строку Windows.

Подключение к базе данных

Когда у вас откроется командная строка, используйте утилиту SQLCMD для подключения к базе данных. В этом примере мы подключаемся к базе данных AdventureWorks2021, поэтому используем команду:

 sqlcmd -d AdventureWorks2021 

При этом используются учетные данные Windows по умолчанию для подключения к вашей базе данных. Вы также можете указать имя пользователя, используя флаг -U, и пароль, используя флаг -P. Например, вы можете подключиться к базе данных, используя имя пользователя «mike» и пароль «goirish», используя следующую командную строку:

 sqlcmd -U майк -P goirish -d AdventureWorks2021 

Пример использования 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)

Пример использования 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

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

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

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

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

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

Похожее:

«Изменение размера шрифта для устройств Nokia по сравнению с изменением общего масштаба шрифта приложения на Android с использованием Configuration.fontScale»? SQL Server: учимся работать с SQLCMD Что такое файл hosts, зачем он нужен и как его исправить? Пошаговое руководство по SQLCMD