1. Главная страница » Компьютеры » Ms sql dynamic sql

Ms sql dynamic sql

Автор: | 16.12.2019

В данном материале мы поговорим о выполнении динамического T-SQL кода, Вы узнаете, как сформировать текстовую строку, содержащую SQL инструкцию, и запустить ее на выполнение в 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, и добавим в нее несколько строк.

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

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

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

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

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

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

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

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

Хранимая процедура 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.

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

ОБЛАСТЬ ПРИМЕНЕНИЯ: SQL Server База данных SQL Azure Azure Synapse Analytics (хранилище данных SQL) Parallel Data Warehouse APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse

Выполняет инструкцию Transact-SQL Transact-SQL или пакет инструкций, которые могут выполняться много раз или создаваться динамически. Executes a Transact-SQL Transact-SQL statement or batch that can be reused many times, or one that has been built dynamically. Инструкция Transact-SQL Transact-SQL или пакет инструкций могут содержать параметры. The Transact-SQL Transact-SQL statement or batch can contain embedded parameters.

Компиляция инструкций Transact-SQL Transact-SQL во время выполнения подвергает приложения риску злонамеренного воздействия. Run time-compiled Transact-SQL Transact-SQL statements can expose applications to malicious attacks.

Синтаксические обозначения в Transact-SQL Transact-SQL Syntax Conventions

Синтаксис Syntax

Аргументы Arguments

[ @stmt =] инструкции [ @stmt= ] statement
Строка в Юникоде, содержащий Transact-SQL Transact-SQL инструкции или пакета. Is a Unicode string that contains a Transact-SQL Transact-SQL statement or batch. @stmt должен быть константой или переменной в Юникоде. @stmt must be either a Unicode constant or a Unicode variable. Более сложные выражения Юникода, например объединение двух строк с помощью оператора +, недопустимы. More complex Unicode expressions, such as concatenating two strings with the + operator, are not allowed. Символьные константы недопустимы. Character constants are not allowed. Если указана константа Юникода, он должен начинаться с префикса N. Например, константа Юникода N ‘sp_who’ является допустимым, а символьная константа ‘sp_who’ не является. If a Unicode constant is specified, it must be prefixed with an N. For example, the Unicode constant N’sp_who’ is valid, but the character constant ‘sp_who’ is not. Размер строки ограничивается только доступной серверу баз данных памятью. The size of the string is limited only by available database server memory. На 64-разрядных серверах, размер строки ограничен 2 ГБ, максимальный размер nvarchar(max) . On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max).

Читайте также:  Dism проверка целостности windows 10

@stmt может содержать параметры, называющиеся аналогично как имя переменной, например: N’SELECT * FROM HumanResources.Employee WHERE Employee > @stmt can contain parameters having the same form as a variable name, for example: N’SELECT * FROM HumanResources.Employee WHERE Employee >

Каждый параметр, включенный в @stmt должен иметь соответствующую запись в @список определений параметров params, а параметр списка значений. Each parameter included in @stmt must have a corresponding entry in both the @params parameter definition list and the parameter values list.

[ @params =] N’@parameter_name data_type [. n ] " [ @params= ] N’@parameter_name data_type [ . n ] ‘
Строка, содержащая определения всех параметров, внедренных в @stmt. Строка должна представлять собой константу в Юникоде либо переменную в этом же формате. Is one string that contains the definitions of all parameters that have been embedded in @stmt. The string must be either a Unicode constant or a Unicode variable. Определение каждого параметра состоит из имени параметра и типа данных. Each parameter definition consists of a parameter name and a data type. n — заполнитель, указывающий Дополнительные определения параметра. n is a placeholder that indicates additional parameter definitions. Каждый параметр, указанный в @stmt должен быть определен в @params. Every parameter specified in @stmt must be defined in @params. Если Transact-SQL Transact-SQL инструкция или пакет в @stmt не содержит параметров, @params не является обязательным. If the Transact-SQL Transact-SQL statement or batch in @stmt does not contain parameters, @params is not required. Этот аргумент по умолчанию принимает значение NULL. The default value for this parameter is NULL.

[ @param1 =] ‘значение1" [ @param1= ] ‘value1
Значение для первого параметра, определенного в строке параметров. Is a value for the first parameter that is defined in the parameter string. Это значение может быть константой или переменной в Юникоде. The value can be a Unicode constant or a Unicode variable. Должно быть значение параметра, предоставленные каждому параметру в @stmt. Значения не являются обязательными при Transact-SQL Transact-SQL инструкция или пакет в @stmt не имеет параметров. There must be a parameter value supplied for every parameter included in @stmt. The values are not required when the Transact-SQL Transact-SQL statement or batch in @stmt has no parameters.

[ OUT | OUTPUT ] [ OUT | OUTPUT ]
Показывает, что параметр процедуры является выходным. Indicates that the parameter is an output parameter. текст, ntext, и изображение параметров можно использовать в качестве ВЫХОДНЫХ параметров, если процедура не является общие процедуры языка среды выполнения (CLR). text, ntext, and image parameters can be used as OUTPUT parameters, unless the procedure is a common language runtime (CLR) procedure. Выходным параметром с ключевым словом OUTPUT может быть заполнитель курсора, если процедура не является процедурой CLR. An output parameter that uses the OUTPUT keyword can be a cursor placeholder, unless the procedure is a CLR procedure.

n n
Заполнитель для значений дополнительных параметров. Is a placeholder for the values of additional parameters. Значения могут быть только константами и переменными. Values can only be constants or variables. Значения не могут представлять собой сложные выражения, такие как функции или выражения, построенные с помощью операторов. Values cannot be more complex expressions such as functions, or expressions built by using operators.

Значения кода возврата Return Code Values

0 (успешное завершение) или ненулевое значение (неуспешное завершение) 0 (success) or non-zero (failure)

Результирующие наборы Result Sets

Возвращает результирующие наборы всех заданных инструкций SQL, встроенные в строку SQL. Returns the result sets from all the SQL statements built into the SQL string.

Примечания Remarks

параметры процедуры sp_executesql должны вводиться в определенном порядке, как описано в разделе «Синтаксис» ранее в этом разделе. sp_executesql parameters must be entered in the specific order as described in the "Syntax" section earlier in this topic. Если параметры вводятся не в этом порядке, будет выдано сообщение об ошибке. If the parameters are entered out of order, an error message will occur.

Относительно пакетов инструкций, области имен и контекста базы данных процедура sp_executesql ведет себя аналогично инструкции EXECUTE. sp_executesql has the same behavior as EXECUTE with regard to batches, the scope of names, and database context. Transact-SQL Transact-SQL Инструкция или пакет в sp_executesql @stmt не компилируются до выполнения инструкции sp_executesql. The Transact-SQL Transact-SQL statement or batch in the sp_executesql @stmt parameter is not compiled until the sp_executesql statement is executed. Содержимое @stmt затем компилируется и выполняется в качестве плана выполнения, отдельный от плана выполнения пакета, вызвавшего процедуру sp_executesql. The contents of @stmt are then compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql. Пакет, содержащийся в процедуре sp_executesql, не может ссылаться на переменные, объявленные в пакете, вызвавшем sp_executesql. The sp_executesql batch cannot reference variables declared in the batch that calls sp_executesql. Локальные курсоры или переменные в пакете sp_executesql недоступны пакету, вызвавшему sp_executesql. Local cursors or variables in the sp_executesql batch are not visible to the batch that calls sp_executesql. Изменения в контексте базы данных длятся только до завершения выполнения инструкции sp_executesql. Changes in database context last only to the end of the sp_executesql statement.

Читайте также:  Kyocera 2035 сетевое сканирование

Процедура sp_executesql может использоваться вместо хранимых процедур для многократного выполнения инструкций Transact-SQL Transact-SQL , где единственные различия между инструкциями — значения параметров. sp_executesql can be used instead of stored procedures to execute a Transact-SQL Transact-SQL statement many times when the change in parameter values to the statement is the only variation. Так как инструкция Transact-SQL Transact-SQL сама остается неизменной и меняются только значения параметров, оптимизатор запросов SQL Server SQL Server , вероятнее всего, повторно использует план выполнения, сформированный перед первым выполнением. Because the Transact-SQL Transact-SQL statement itself remains constant and only the parameter values change, the SQL Server SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.

Для улучшения производительности используйте полные имена объектов в строке инструкции. To improve performance use fully qualified object names in the statement string.

Хранимая процедура sp_executesql поддерживает задание значений параметрам отдельно от строки Transact-SQL Transact-SQL , как показано в следующем примере. sp_executesql supports the setting of parameter values separately from the Transact-SQL Transact-SQL string as shown in the following example.

Выходные параметры также могут быть использованы sp_executesql. Output parameters can also be used with sp_executesql. В следующем примере название задания получается из таблицы AdventureWorks2012.HumanResources.Employee и возвращается в выходном параметре @max_title . The following example retrieves a job title from the AdventureWorks2012.HumanResources.Employee table and returns it in the output parameter @max_title .

Возможность подставлять разные значения параметров в sp_executesql предоставляет следующие преимущества перед использованием инструкции EXECUTE. Being able to substitute parameters in sp_executesql offers the following advantages to using the EXECUTE statement to execute a string:

Так как собственно текст инструкции Transact-SQL Transact-SQL в строке sp_executesql не меняется между выполнениями, оптимизатор запросов, вероятнее всего, сопоставит инструкцию Transact-SQL Transact-SQL во время второго выполнения с планом выполнения, сформированным во время первого выполнения. Because the actual text of the Transact-SQL Transact-SQL statement in the sp_executesql string does not change between executions, the query optimizer will probably match the Transact-SQL Transact-SQL statement in the second execution with the execution plan generated for the first execution. Следовательно, компиляция второй инструкции SQL Server SQL Server не обязательна. Therefore, SQL Server SQL Server does not have to compile the second statement.

Строка Transact-SQL Transact-SQL строится только один раз. The Transact-SQL Transact-SQL string is built only one time.

Целочисленный параметр определен в собственном формате. The integer parameter is specified in its native format. Приведение к Юникоду не требуется. Casting to Unicode is not required.

Разрешения Permissions

Требуется членство в роли public. Requires membership in the public role.

Примеры Examples

A. A. Выполнение простой инструкции SELECT Executing a simple SELECT statement

В следующем примере создается и выполняется простая инструкция SELECT , содержащая внедренный параметр с именем @level . The following example creates and executes a simple SELECT statement that contains an embedded parameter named @level .

Б. B. Выполнение динамически построенной строки Executing a dynamically built string

В следующем примере показано использование процедуры sp_executesql для выполнения динамически построенной строки. The following example shows using sp_executesql to execute a dynamically built string. В этом примере хранимая процедура вставляет данные в набор таблиц, использующихся для секционирования данных о продажах по одному году. The example stored procedure is used to insert data into a set of tables that are used to partition sales data for a year. Для каждого месяца года создается одна таблица следующего формата: There is one table for each month of the year that has the following format:

В этом образце хранимая процедура динамически строит и выполняет инструкцию INSERT для вставки новых заказов в соответствующую таблицу. This sample stored procedure dynamically builds and executes an INSERT statement to insert new orders into the correct table. В этом примере используется дата заказа для формирования имени таблицы, которая должна содержать данные, затем полученное имя вставляется в инструкцию INSERT . The example uses the order date to build the name of the table that should contain the data, and then incorporates that name into an INSERT statement.

Это простой пример использования процедуры sp_executesql. This is a simple example for sp_executesql. Пример не включает в себя проверку ошибок и бизнес-правил, которые, например гарантируют то, что номера заказов не будут дублироваться в разных таблицах. The example does not contain error checking and does not include checks for business rules, such as guaranteeing that order numbers are not duplicated between tables.

Применение процедуры sp_executesql в этом случае более эффективно, чем использование инструкции EXECUTE для выполнения строки. Using sp_executesql in this procedure is more efficient than using EXECUTE to execute a string. При использовании процедуры sp_executesql формируется только 12 версий инструкции INSERT, по одной для таблицы каждого месяца. When sp_executesql is used, there are only 12 versions of the INSERT string that are generated, one for each monthly table. При использовании EXECUTE каждая инструкция INSERT должна быть уникальной, так как значения параметров будут различными. With EXECUTE, each INSERT string is unique because the parameter values are different. И хотя с помощью обоих методов будет создано одинаковое число пакетов, подобие инструкций INSERT, сформированных sp_executesql, увеличивает вероятность того, что оптимизатор запросов повторно использует планы выполнения. Although both methods generate the same number of batches, the similarity of the INSERT strings generated by sp_executesql makes it more likely that the query optimizer will reuse execution plans.

В. C. Использование параметра OUTPUT Using the OUTPUT Parameter

В следующем примере используется OUTPUT параметр для хранения результирующего набора, формируемого SELECT инструкции в @SQLString параметра. Два SELECT затем выполняются инструкции, использующие значение OUTPUT параметра. The following example uses an OUTPUT parameter to store the result set generated by the SELECT statement in the @SQLString parameter.Two SELECT statements are then executed that use the value of the OUTPUT parameter.

Читайте также:  Noctua nh u12s se am4 обзор

Примеры: Azure Synapse Analytics (хранилище данных SQL) Azure Synapse Analytics (SQL DW) и Параллельное хранилище данных Parallel Data Warehouse Examples: Azure Synapse Analytics (хранилище данных SQL) Azure Synapse Analytics (SQL DW) and Параллельное хранилище данных Parallel Data Warehouse

Г. D. Выполнение простой инструкции SELECT Executing a simple SELECT statement

В следующем примере создается и выполняется простая инструкция SELECT , содержащая внедренный параметр с именем @level . The following example creates and executes a simple SELECT statement that contains an embedded parameter named @level .

В наших проектах нам приходится решать различные задачи. Для решения некоторых из них мы используем dynamic T-Sql (далее по тексту dynamic sql).

Для чего нужен dynamic sql? Каждый решает для себя. В одном из проектов с помощью dynamic sql мы решили задачи построения динамичных отчетов, в других — миграцию данных. Также dynamic sql незаменим в случаях, когда требуется создать/изменить/получить данные или объекты, но значения/названия приходят в качестве параметров. Да, это может показаться абсурдом, но есть и такие задачи.

Дальше мы покажем несколько примеров, как это можно реализовать с помощью dynamic sql.

Выполнить динамическую команду можно несколькими способами:

  • С использование ключевого слова EXEC/EXECUTE ;
  • C использование хранимой процедуры sp_executesql

Данные способы отличаются между собой кардинально. На небольшом примере мы постараемся пояснить, чем они отличаются.

Как видно из запроса выше, мы формируем динамическую команду. Если выполнить select @sql , то результат будет следующий:

Что же тут плохого? — Запрос отработает, и все будут довольны. Но все же, есть несколько причин, почему так делать не стоит:

  1. При написании команды очень легко ошибиться с количеством «’», т.к. необходимо указывать дополнительные «’», чтобы передать текстовое значение в запрос.
  2. При таком запросе возможны Sql инъекции (SQL Injection). Например, стоит задать значение для @city вроде такого
    — и результат будет печальный, т.к. операция select выполнится успешно, как и операция DROP TABLE customers .
  3. Возможна ситуация, когда у вас будет несколько переменных, содержащих коды ваших команд. Что-то типа такой EXEC(@sql1 + @sql2 + @sql3).
    Какие трудности могут возникнуть тут?
    Нужно помнить, что каждая команда отработает отдельно, хотя на первый взгляд, может показаться, что будет выполнена операция конкатенации (@sql1 + @sql2 + @sql3) , а затем выполнится общая команда. Также нужно помнить, что накладывается общее ограничение на параметр команды EXEC в 4000 символов.
  4. Происходит неявное приведение типов, т.к. параметры передаются в виде строки.

Что изменится при использовании sp_executesql ? – Разработчику проще писать код и его отлаживать, т.к. код будет написан практически как обычный Sql запрос.

Что же изменилось?

  1. В отличие от EXECUTE при использовании sp_executesql , не нужно никакое приведение типов, если мы используем типизированные параметры sp_executesql.
  2. Это решает проблему с дополнительными «’».
  3. Решается проблема безопасности — Sql инъекции (SQL Injection).

Для обоих подходов планы запросов кэшируются, но они отличаются. Эти отличия приведены на рисунке 1 и рисунке 2.

Также одно из преимуществ использования sp_executesql – это возможность возвращать значение через OUT параметр.

Далее приведем пример, как мы решили одну из проблем в проекте с использованием dynamic sql.

Допустим, у нас есть товар (да неважно, собственно, что это: товар, анкета на должность, персональная анкета). Смысл в том, что каждый объект имеет свой набор свойств (атрибутов), который его характеризует, а их может быть разное количество, и они будут разного типа. Как хранить в БД – это проблема архитектуры.

Для клиента нужен был отчет, который из себя представлял n строк на m столбцов. Где m и был наш набор атрибутов. Отчет собирался по группе объектов или для какого-то объекта из группы. Но смысл остается все тот же: каждый отчет содержит разное количество столбцов для каждой группы объектов.

Поскольку изначально существовала связь между объектами, то решение проблемы выбрали без изменения архитектуры БД. На наш взгляд, решений данной проблемы может быть несколько:

  • Использовать систему отчетности, например, MS Sql Reporting Service. Создать матричный отчет, а в качестве запроса у нас будет «простой» Select . Почему мы так не сделали? В проекте не так много было отчетов, чтобы внедрять туда SSRS.
  • Использовать тот же «простой» select и на серверной стороне уже создавать DataSet необходимой «формы». Да, так задача была решена изначально, когда данных о товарах было очень мало. Как только данных стало достаточно много, то время сбора отчета стало выходит за установленный timeout.
  • Использовать Pivot в sql. Да, отличное решение, когда вы знаете, что у вас только эти атрибуты, и новых не будет. А что делать, когда количество атрибутов часто меняется. И опять же, для каждой группы объектов у нас свой набор атрибутов, мы снова вернемся к созданию процедуры для каждой группы объектов. Не очень удобное решение, не правда ли?
  • А если использовать Pivot, но добавить туда немного dynamic sql? – Да, это решение, которое имеет право на жизнь. Его мы и опишем, как пример использования dynamic sql…

Ссылка на скрипты для создания таблиц и запроса.

В основе отчета будет лежать обычный запрос:

Давайте рассмотрим, что же мы тут написали:

  • Инициализируем переменную со значением нашей категории товаров — declare @CategoryOfProducts >
  • Далее нам нужно получить список колонок для нашей категории товаров, но при этом они должны быть заключены в “[]” скобки и перечислены через “,” как этого требует синтаксис функции Pivot —

Ну а дальше все просто: при выполнении кода список колонок для функции Pivot будет подставлен из @PivotColumnHeaders

Если выполнить select @PivotTableSQL , то мы получим тот запрос, который без использования dynamic sql нам бы пришлось писать вручную.

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

В заключение стоит еще раз отметить используя dynamic sql, мы можем решать на первый взгляд нетривиальные задачи тривиальными способами. Для этого порой требуется посмотреть на проблему с другой стороны.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *