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

Working with the sql server command line (sqlcmd)

You can run sqlcmd as commands. You can run scripts in command mode.

  • How to run a T-SQL script and receive the output in a file in sqlcmd

    In the next example, we will show how to run a script using sqlcmd and show the results in another file.

    We will first create a script file named columns.sql with the following sentences:

    select * from adventureworks2021.information_schema.columns

    In the cmd, run the following command to invoke sqlcmd:

    sqlcmd -S DESKTOP-5K4TURFSQLEXPRESS -E -i c:sqlcolumns.sql -o c:sqlexit.txt

    -i is used to specify the input. You specify the script file with the queries.
    -o is used to show the results of the input in a file.

    The exit.txt file will be created:

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

    If we open the file, we will see the output results:

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

  • How to back up in sqlcmd

    We will first create a script to back up the database named backup.sql:

    In the cmd run the following command:

    sqlcmd -S DESKTOP-5K4TURFSQLEXPRESS -E -i c:sqlbackup.sql -o
    c:sqloutput.txt

    The output will be similar to this one:

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

    The commands will create a backup in a file named backup.sql in the c:sql folder:

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

  • How to work with variables in sqlcmd

    You can work with variables in sqlcmd. The following example will set the variable DATABASENAME with the value adventureworks2021 and then we change the context to the database specified:

    The result displayed is the following:

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

    As you can see, SETVAR is used to specify the value of the variable. Then you need to use $() for the variable.

    Another example is to set the variable CONTACTTYPEID to 3 and use it in the where clause to find a contact type ID according to the value of the variable:

    The result displayed is the following:

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

  • How to list the table names of a database in sqlcmd

    You can list the tables of the database using the information_schema.tables view. We will first create a script named tables.sql. This script contains the tables and views:

    Next, we will invoke sqlcmd to execute the script.

    sqlcmd -E -i c:sqltables.sql -o c:sqloutput.txt -S DESKTOP-

    5K4TURFSQLEXPRESS

    The result displayed are the following in the output.txt file:

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

  • How to list the column names of a database in sqlcmd

    The following sentences will list the table names and the column names of a database in a script named columns.sql:

    In the cmd run this command:

    sqlcmd -E -i c:sqlcolumns.sql -o c:sqloutput.txt -S DESKTOP-

    5K4TURFSQLEXPRESS

    The result of the output.txt is the following:

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

  • How to check all the commands

    You can check all the sqlcmd commands using this command:

    Sqlcmd -?

    This command will list all the commands available:

    :/>  Глава 2. Примеры использования  стандартных объектов WSH (JScript и VBScript). «Windows Script Host для Windows 2000/XP» | Попов Андрей Владимирович

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

  • How to exit if the command fails

    The following command will exit if it fails using the –b parameter:

    sqlcmd -E -q “create table adventureworks” –b -S DESKTOP-

    5K4TURFSQLEXPRESS

    The command will exit if there is an error:

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

  • How to display error messages according to the error level

    If there is an error, the error is displayed. However, according to the error level, you can stop this behavior by default using the -m option.

    Here it is an example about this:

    The following command shows an error message:

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

    However, if you add the –m 16, the error will no longer be displayed because the error has the level of 15:

    sqlcmd -E -q “create table adventureworks” -m 16 -S

    DESKTOP-5K4TURFSQLEXPRESS

    -m 16 will show only the errors higher than 16. As you can see the error message is no longer displayed

  • How to accept user input

    The following example will run a SQL script with one variable. The example will create a database specified by the user.

    We will first create a script named createdb.sql with the following content:

    Next, in the cmd we will run the database specifying the database name:

    sqlcmd -E -v DATABASENAME=”Userinput” -i

    c:sqlcreatedb.sql

    The command will create a database named Userinput.

    In sqlcmd you can run the sp_databases stored procedure:

    And you will be able to see the database created:

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

  • Ввод запроса

    Начните вводить оператор 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.

    :/>  В чем разница виндовс 10 pro и домашняя

    Динамическая 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.

    :/>  Удаление обновлений Windows 7 — 3 способа

    Текст запроса будет храниться в переменной @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).

    Оставьте комментарий