EXECUTE ({<string variable><literal command string> })

Как и при вызове на выполнение хранимых процедур, допускается использовать и то и другое ключевое слово (EXEC и EXECUTE).

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

USE AdventureWorks GO

Создание таблицы, в которой будет представлена информация -- для динамического кода SQL CREATE TABLE DynamicSQLExample (

TablelD int IDENTITY NOT NULL

CONSTRAINT PKDynamicSQLExample PRIMARY KEY, SchemaName varchar(128) NOT NULL,

TableName varchar(128) NOT NULL

/* Заполнение таблицы. На этот раз используются данные обо всех пользовательских ** таблицах в базе данных

INSERT INTO DynamicSQLExample

SELECT s.name AS SchemaName, t.name AS TableName FROM sys.schemas s JOIN sys.tables t ON s.schema id = t.schema id

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

(75 row(s) affected)

Рассматривая полученные результаты, следует помнить традиционную оговорку рек-ламщиков: Фактически результаты могут оказаться немного инымиИначе говоря, состав полученных результатов зависит от того, какие примеры из книги были вами выполнены, какие вы пропустили и в отношении каких таблиц проявили инициативу, уничтожив следы выполнения упражнений после их завершения. Так или иначе, вышеназванные различия не имеют особого значения.

Итак, на данный момент сформирован список всех таблиц в текущей базе данных. Теперь предположим, что требуется обеспечить выборку некоторых данных, хранящихся в одной из таблиц, но таблица должна быть указана только на этапе прогона с помощью ее идентификатора. Например, выборка всех данньгх из таблицы с идентификатором 13 может быть выполнена следующим образом:

DECLARE ©SchemaName varchar(128)

DECLARE ©TableName varchar(128)

-- Определить имя таблицы, имеющей указанный идентификатор SELECT ©SchemaName = SchemaName, ©TableName = TableName

FROM DynamicSQLExample

WHERE TablelD = 13

-- Наконец, это значение передается в оператор EXEC EXEC (SELECT * FROM + ©SchemaName + . + ©TableName)

Если имена таблиц, собранные в таблице DynamicSQLExample при выполнении этого упражнения читателем, совпадают с именами, полученными автором, то идентификатор TablelD, равный 13, должен соответствовать таблице Categories. В этом случае должны быть получены примерно такие результаты, как показано в табл. 10.2 (в целях сокращения объема таблицы правые столбцы были исключены).

Таблица 10.2. Результаты выполнения динамически сформированного кода SQL

VendorlD AccoiintNuxnber Name

1 INTERNATOOOl International

2 electron0002 Electronic Bike Repair & Supplies

3 PREMIEROOOl Premier Sport, Inc.

4 COMFORTOOOl Comfort Road Bicycles

Нарушения в работе оператора exec

Оператор EXEC относится к числу привлекательных и перспективных средств, но при его использовании обычно приходится проводить небольшие эксперименты и преодолевать определенные сложности. Ниже описаны основные нюансы, с которыми приходится сталкиваться при применении оператора EXEC.

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

Оператор EXEC выполняется в том же контексте защиты, в котором работает текущий пользователь, а не в контексте защиты вызывающего объекта.

Оператор EXEC выполняется в том же контексте соединения и транзакцион-ном контексте, что и вызывающий объект (дополнительная информация на эту тему приведена в главе 12).

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

EXEC не может использоваться в пользовательской функции.

Каждое из этих требований довольно сложно понять без дополнительного пояснения, поэтому рассмотрим каждое из них отдельно.

Домен, в котором действует оператор exec

Принципы, на основании которых устанавливается область определения переменных в операторе EXEC, с трудом поддаются пониманию. Но фактически строка с оператором, в которой происходит вызов оператора EXEC, имеет ту же область

определения, что и остальная часть пакета (или процедуры), в котором выполняется оператор EXEC, хотя код, вызываемый на выполнение в результате применения оператора EXEC, рассматривается как принадлежащий к отдельному пакету. Поскольку такая ситуация встречается часто, проще всего показать ее на примере: USE AdventureWorks

/* Вначале объявляются переменные. Одна из них предназначена для хранения данных, ** передаваемых в оператор EXEC, а другая - для получения возвращаемых данных (но ** в данном случае возврат не происходит) */

DECLARE @InVar varchar(50) DECLARE ©OutVar varchar(50)

-- Подготовка строки, предназначенной для передачи в команду EXEC SET ©InVar = SELECT ©OutVar = FirstName FROM Person.Contact WHERE ContactID = 1

-- Выполнение команды EXEC (©Invar)

-- Теперь, чтобы убедиться в том. что результат от этого не зависит, вызовем --на выполнение оператор SELECT непосредственно, без использования -- переменной ©InVar

EXEC (SELECT ©OutVar = FirstName FROM Person.Contact WHERE ContactID =1)

-- Переменная ©OutVar будет по-прежнему иметь NULL-значение, поскольку в нее не -- удается поместить какие-либо данные SELECT ©OutVar

Теперь рассмотрим полученные результаты:

Msg 137, Level 15, State 1, Line 1

Must declare the scalar variable ©OutVar.

Msg 137, Level 15, State 1, Line 1

Must declare the scalar variable ©OutVar.

NULL

(1 row(s) affected)

Очевидно, что СУБД SQL Server сразу же сообщает об ошибке и не может предпринять каких-либо дальнейших действий. Но следует пояснить, почему получено сообщение об ошибке, указывающее на отсутствие объявления, Must Declare , тогда как переменная ©OutVar уже объявлена. Безусловно, эта переменная объявлена во внешней области определения, но не в самом операторе EXEC.

Рассмотрим, что произойдет, если тот же сценарий будет организован несколько иначе:

USE AdventureWorks

-- На этот раз требуется только одна переменная, но потребность в ней сохраняется -- в течение более продолжительного времени DECLARE ©InVar varchar(200)

/* Подготовка строки для передачи в команду EXEC. На этот раз предпринимается ** попытка передать одновременно несколько операторов. Все эти операторы ** выполняются в виде одного пакета */

SET ©InVar = DECLARE ©OutVar varchar(50)

SELECT ©OutVar = FirstName FROM Person.Contact

WHERE ContactID = 1 SELECT The Value Is + ©OutVar