Главная » Microsoft SQL Server, Базы данных » Динамический SQL

0

Термин динамический SQL имеет два противоречивых определения. Некоторые говорят, что он подразумевает запрос, выполняемый клиентом, а не хранимой процедурой. Точнее бы было сказать, что он описывает любую инструкцию SQL DML, динамически генерируемую в процессе выполнения программы.

особенно полезен в следующих ситуациях.

?               Для сборки предложения WHERE из множества возможных критериев.

?               Для сборки предложения FROM, включающего только те таблицы и объединения, которые необходимы предложению WHERE.

?               Для создания динамического предложения ORDER BY, по-разному упорядочивающего данные в зависимости от потребностей пользователя.

Выполнение инструкций динамического SQL

Команда execute (или сокращенно ехес) создает новый экземпляр пакета, как если бы выполняемый код был хранимой процедурой. Несмотря на то что команда execute обычно используется для вызова хранимых процедур, ее также можно использовать для запуска пакетов и запросов T-SQL:

ЕХЕС[UTE] (‘пакет_Т- SQL)

WITH RECOMPILE;

Параметр WITH RECOMPILE заставляет SQL Server перекомпилировать пакет и создать новый план выполнения запроса. Если строка Т-SQL и ее параметры подвержены сильным изменениям, то параметр WITH RECOMPILE позволит избежать выполнения ошибочного плана. В то же время, если строка Т-SQL не подвержена изменениям, ненужный процесс перекомпиляции только замедлит выполнение запроса. Большинство процедур динамического SQL создают в корне отличающиеся запросы SQL, так что параметр WITH RECOMPILE в данном случае обычно уместен.

Например, следующая команда ехес выполняет обычную инструкцию SELECT:

USE Family;

ЕХЕС (‘Select LastName from Person Where PersonID = 12 ‘ ) ;

Будет получен следующий результат:

LastName

Halloway

При работе с командой ехес следует учитывать контекст безопасности испол- Новинка 1 няемого кода. В версии SQL Server 2005 введен синтаксис execute as для яв- 2005          ного задания контекста безопасности. Подробно контекст безопасности и син

таксис команды execute as рассматриваются в главе 40.

Новинкой в команде execute является возможность выполнения кода на свя- Новинка " занном, а не только на локальном сервере. Следующий код передается на уда- 2005       ленный сервер, а результаты возвращаются на локальный:

ЕХЕС[UTE] {код) АТ имя_связанного_сервера

sp_executeSQL

Новый метод выполнения динамического SQL заключается в использовании системной хранимой процедуры sp_exe cute SQL. Этот метод больше подходит для сложных запросов, чем обычная команда execute. Я заметил, что в некоторых ситуациях команда exceute не справлялась с выполнением динамических запросов, в то время как процедура sp_exe cute SQL работала безукоризненно.

ЕХЕС Sp_ExecuteSQL 1запрос T-SQL1, определение_параметров, параметр, параметр…

В строке 4запрос T-SQU не допускается конкатенация строк, поэтому параметры не указываются. Сам запрос и определения должны использовать таблицу символов Unicode.

Параметры хранимой процедуры предназначены для оптимизации. Если запрос T-SQL при каждом запуске использует одни и те же параметры, то они могут быть переданы хранимой процедуре sp_exe cute SQL, чтобы сформировать план выполнения запроса. В следующем примере выполняется тот же запрос к таблице Person базы данных Family, но в данном случае использованы параметры (буква N необходима, так как должны использоваться строки в кодировке Unicode):

EXEC sp_executeSQL N’Select LastName From Person

Where PersonID = @PersonSelect1,

N1©PersonSelect INT1,

@PersonSelect = 12;

Результат будет получен тот же:

LastName

Halloway

Создание динамического кода SQL

Создание динамических строк кода SQL обычно предполагает комбинирование выражения SELECT COLUMNS с более гибкими предложениями FROM и WHERE.

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

В примере битовая переменная Needs And отслеживает потребности и с помощью связки AND объединяет их в предложении WHERE. Если задана категория товара, то начальная часть инструкции SELECT включает в себя все необходимые объединения таблицы ProductCategory. Предложение WHERE обследует все возможные критерии пользователя. Если пользователь задал для столбца некоторый критерий, то он вместе с этим критерием добавляется в строку @SQLWhere.

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

USE OBXKites;

DECLARE

@SQL NVARCHAR(1024),

@SQLWhere NVARCHAR(1024),

@NeedsAnd BIT,

– Пользовательские параметры @ProductName VARCHAR(50),

@ProductCode VARCHAR(IO),

@ProductCategory VARCHAR(50);

– Инициализация переменных SET @NeedsAnd = 0;

SET @SQLWhere = ‘ ‘ ;

– Имитация требований пользователя SET ©ProductName = NULL;

SET @ProductCode = 1001;

SET @ProductCategory = NULL;

– Сборка динамического запроса — Настройка начальной части инструкции IF @ProductCategory IS NULL

SET @SQL = ‘Select ProductName from Product’

ELSE

SET @SQL = ‘Select ProductName from Product Join ProductCategory

on Product.ProductCategorylD

= ProductCategory.ProductCategorylD';

– Сборка динамического предложения WHERE IF @ProductName IS NOT NULL BEGIN;

SET @SQLWhere = ‘ProductName = ‘ + @ProductName;

SET @NeedsAnd = 1;

END;

IF @ProductCode IS NOT NULL BEGIN;

IF ©NeedsAnd = 1

SET @SQLWhere = @SQLWhere + ‘ and ‘;

SET ©SQLWhere = ‘Code = ‘ + @ProductCode;

SET @NeedsAnd = 1;

END;

IF ©ProductCategory IS NOT NULL BEGIN;

IF @NeedsAnd = 1

SET @SQLWhere = @SQLWhere + ‘ and ‘;

SET @SQLWhere = ‘ProductCategory = ‘ + @ProductCategory;

SET @NeedsAnd = 1;

END;

– Окончательная сборка запроса SELECT IF @SQLWhere <> ”

SET @SQL = @SQL + ‘ WHERE ‘ + ©SQLWhere Print @SQL

EXEC sp_executeSQL @SQL WITH RECOMPILE

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

Select Name from Product where Code = 1001;

Name

Basic Box Kite 21 inch

Дополнительная Метод динамического аудита использует сложный запрос SQL в хранимой про- чнформаци?. цедуре. Этот метод мы подробно рассмотрим в главе 24.

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии