Обоснование использования динамических SQL запросов

По материалам статьи Robert Marda на сайте sqlservercentral.com: When to Use Dynamic SQL

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

Таблицы, необходимые для демонстрационных примеров

С помощью представленных ниже скриптов, можно создать необходимые для демонстрационных примеров таблицы и заполнит их данными:

CREATE TABLE #ColumnNames (ColumnID int IDENTITY (1, 1) NOT NULL, 
                           ColumnName varchar (50) NULL)

CREATE TABLE #ColumnValues (ColumnID int NOT NULL, 
                            Value varchar (20) NULL, 
                            ClientName varchar (50) NULL)

INSERT INTO #ColumnNames (ColumnName)
SELECT 'Has Special License' UNION
SELECT 'Dealer'

INSERT INTO #ColumnValues (ColumnID, Value, CLientName)
SELECT 1,'Yes','Database Hobby Shoppe' UNION
SELECT 2,'No','Imaginary Databases Inc' UNION
SELECT 2,'Yes','Super Databases Management'

Пример №1

Этот первый пример показывает, как получить ColumnName из существующей таблицы и использовать это, как алиас для столбца, который содержит соответствующие данные. Это одно из возможных решений, с помощью которого можно предоставить возможность пользователям создавать их собственные столбцы и заполнять их значениями посредством обычной вставки в обе таблицы. В реальных применениях используются не временные таблицы, как в этом примере. Если Вы решите предоставить возможность пользователям выполнять подобные операции, единственным способом возвращения им требуемых результирующих наборов, будет использование ColumnName в динамическом запросе. В примере представлено одно из возможных решений, с помощью которого Вы можете решить описанную выше задачу:

DECLARE @Query varchar(300), @ColumnName varchar(50)
SELECT @ColumnName = ColumnName
FROM #ColumnNames
WHERE ColumnID = 2
SET @Query = '
SELECT ClientName, Value AS [' + @ColumnName + ']
FROM #ColumnValues v
INNER JOIN #ColumnNames n ON n.ColumnID = v.ColumnID
WHERE n.ColumnID = 2'
PRINT @Query
PRINT ''
EXEC (@Query)

Представленный в примере алгоритм полезен, когда Вы хотите передать результат, установленного кем - то значения, используя ColumnName добавленного пользователем столбца, по аналогии с электронной таблицей Excel.

Пример №2

Представленный в предыдущем примере алгоритм для ColumnName также может быть применён и к именам таблиц TableName. Если Вам нужно в запросе или в хранимой процедуре получить данные об указанном в переменной имени таблицы, используйте нижеследующий алгоритм:

DECLARE @Query varchar(300), @TableName varchar(30)
SET @TableName = '#ColumnValues'
SET @Query = '
SELECT *
FROM ' + @TableName
PRINT @Query PRINT ''
EXEC (@Query)

Строки, выводимые PRINT, продемонстрируют результат работы алгоритма.

Пример №3

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

DECLARE @InList varchar(30)
SET @InList = '1,2'
SELECT *
FROM #ColumnValues
WHERE ColumnID IN (@InList)

Если Вы используете только одинарные цифры в переменной @InList, представленный выше запрос будет работать. Конечно, если будет использоваться эквивалентный признак для ключевого слова IN. SQL сервер попытается конвертировать список в int, что заработает только если в списке будет одинарные цифры.
Есть несколько способов разрешить эту проблему. Ниже показано, как можно использовать для этого динамический запрос:

DECLARE @InList varchar(30), @Query varchar(300)
SET @InList = '1,2'
SET @Query = '
SELECT *
FROM #ColumnValues
WHERE ColumnID IN (' + @InList + ')'
PRINT @Query PRINT ''
EXEC (@Query)

Строки, выводимые PRINT, используются только для демонстрации просмотра переменной @InList.

Пример №4

Когда разграниченный запятыми список не является списком из чисел, необходимо будет внести небольшие коррективы, для учёта специфики такого списка:

DECLARE @InList varchar(30)
SET @InList = 'yes,no'
SELECT *
FROM #ColumnValues
WHERE Value IN (@InList)

Если в SET @InList необходимо использовать значения 'yes' или "no", всё будет выполнено, как надо. Но, при использовании запятой, SQL сервер будет пытаться вычленять полную символьную строку, куда попадёт и запятая. Есть много способов разрешения этой проблемы, и все они, кроме одного, не используют динамический запрос. Рассмотрим метод с динамическим запросом:

DECLARE @InList varchar(30), @Query varchar(300), @Position int
SET @InList = 'yes,no,maybe'
SET @Position = 0
WHILE @Position <> 1
BEGIN
SET @Position = CHARINDEX (',', @InList, @Position)
IF @Position <> 0
BEGIN
SET @InList = STUFF(@InList,@Position,1,''',''')
SET @Position = @Position + 2
END
ELSE
SET @Position = 1
END
SET @Query = '
SELECT *
FROM #ColumnValues
WHERE Value IN (''' + @InList + ''')'
PRINT @Query PRINT ''
EXEC (@Query)

В этом примере, WHILE LOOP используется для вычленения отдельного значения, необходимого для присвоения переменной @InList, используемой при формировании динамического запроса.

Пример №5

Динамическое формирование большого запроса, основанного на значениях в различных переменных, это то, что вызывает чувство сомнения у большинства администраторов базы данных и разработчиков. Автор считает, что использование одного динамического запроса может заменить порой 10, 20 или более хранимых процедур и избавить от настройки эффективности их исполнения. В следующем примере Вы жертвуете эффективностью ради удобства разработки запроса:

DECLARE @Query varchar(1000), @ClientName varchar(50), @ColumnName varchar (50), 
@ColumnDisplay tinyint, @Debug bit
SET @ClientName = 'y'
SET @ColumnName = ''
SET @ColumnDisplay = 0
SET @Debug = 1
SET @Query = 'SELECT ClientName'
IF @ColumnDisplay > 1
SET @Query = @Query + ',' + CHAR(10) + 'v.ColumnID'
IF @ColumnDisplay > 0
SET @Query = @Query + ',' + CHAR(10) + 'Value'
IF @ColumnDisplay > 0
SET @Query = @Query + ',' + CHAR(10) + 'ColumnName'
SET @Query = @Query + CHAR(10) + 'FROM #ColumnValues v'
IF @ColumnDisplay > 0 OR @ColumnName <> ''
SET @Query = @Query + CHAR(10) + 'INNER JOIN #ColumnNames n ON n.ColumnID = v.ColumnID'
SET @Query = @Query + CHAR(10) + 'WHERE v.ColumnID > 0'
IF @ClientName <> ''
SET @Query = @Query + CHAR(10) + 'AND ClientName LIKE ''%' + @ClientName + '%'''
IF @ColumnName <> ''
SET @Query = @Query + CHAR(10) + 'AND ColumnName = ''' + @ColumnName + ''''
IF @Debug = 1
BEGIN
PRINT @Query PRINT ''
END
EXEC (@Query)
DROP TABLE #ColumnNames
DROP TABLE #ColumnValues

Этот пример очень показателен. Так как многие из Вас, скорее всего, просто скопируют код примера, чтобы опробовать его в действии, автор включил команды DROP TABLE, предвидя то, что Вы можете захотеть изменить значения четырех переменных: @ClientName, @ColumnName, @ColumnDisplay и @Debug. Это может быть сделано, что бы увидеть, как подобные изменения затрагивают структуру запроса. Можно добавить @Debug в запрос и хранимую процедуру с динамическим запросом так, чтобы можно было видеть, как они формируются, причём, без их изменения и с гарантией, что они будут функционировать нормально для всех, кто их будет использовать.
Вот некоторые значения, которые Вы можете попробовать:

@ColumnName = 'dealer'
@ColumnDisplay = 1 and then = 2
@Debug = 0
@ClientName = ''

Заключение

Есть задачи, когда динамический запрос является самым легким и, возможно, единственным решением и может исполняться также как статический запрос. Когда эффективность и быстродействие имеют высший приоритет, тогда Вы должны всячески избегать динамических запросов.