Динамический 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 = ''

Заключение

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