Dynamic T-SQL и как он может быть полезен

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

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

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

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

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

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

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

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

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

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

Также одно из преимуществ использования 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 @CategoryOfProductsId int = 1
Далее нам нужно получить список колонок для нашей категории товаров, но при этом они должны быть заключены в “[]” скобки и перечислены через “,” как этого требует синтаксис функции Pivot —

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

📎📎📎📎📎📎📎📎📎📎