Работа с параметрами и коды возврата в задаче «Выполнение SQL»

В инструкциях SQL и хранимых процедурах часто используются входные параметры input, выходные параметры output и коды возврата. В службах Integration Services задача «Выполнение SQL» поддерживает типы параметров Input, Output и ReturnValue. Используйте тип Input для входных параметров, Output — для выходных и ReturnValue — для кодов возврата.

Примечание
В задаче «Выполнение SQL» параметры можно использовать, только если их поддерживает поставщик данных.

Параметры команд SQL, включая запросы и хранимые процедуры, сопоставлены с пользовательскими переменными, созданными в области задачи «Выполнение SQL», в области родительского контейнера или в области пакета. Значения переменных можно задать во время разработки или динамически заполнить во время выполнения. Также можно сопоставить параметры системным переменным. Дополнительные сведения см. в разделах Переменные служб Integration Services и Системные переменные.

Однако для работы с параметрами и кодами возврата задачи «Выполнение SQL» необходимо знать больше, чем поддерживаемые задачей типы параметров и как сопоставлены эти параметры. Существуют дополнительные требования и рекомендации для успешного использования параметров и кодов возврата в задаче «Выполнение SQL». В оставшейся части раздела приведены эти требования и рекомендации.

• Применение имен и маркеров параметров

• Использование параметров с типами данных даты и времени

• Использование параметров в предложениях WHERE

• Использование параметров с хранимыми процедурами

• Возвращение значений кодов возврата

• Настройка параметров и кодов возврата в редакторе задачи «Выполнение SQL»

Применение имен и маркеров параметров

В зависимости от типа соединения, который использует задача «Выполнение SQL», синтаксис команды SQL использует различные маркеры параметров. Например, тип диспетчера соединений ADO.NET требует, чтобы команда SQL использовала маркер параметра в формате @varParameter, в то время как тип соединения OLE DB требует в качестве маркера параметра символ вопросительного знака (?).

Имена, которые можно использовать как имена параметров в сопоставлениях между переменными и параметрами, также зависят от типа диспетчера соединений. Например, тип диспетчера соединений ADO.NET использует определенные пользователем имена, начинающиеся с префикса @, в то время как тип диспетчера соединений OLE DB требует использования числового значения (начинающегося с нуля) в качестве имени параметра.

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

Тип соединений	Маркер параметра	Имя параметра	Пример команды SQL
ADO	?	Параметр1, параметр2, …	SELECT FirstName, LastName, Title FROM Person.Person WHERE BusinessEntityID = ?
ADO.NET	@<имя параметра>	@<имя параметра>	SELECT FirstName, LastName, Title FROM Person.Person WHERE BusinessEntityID = @parmBusinessEntityID
ODBC	?	1, 2, 3, …	SELECT FirstName, LastName, Title FROM Person.Person WHERE BusinessEntityID = ?
EXCEL и OLE DB	?	0, 1, 2, 3, …	SELECT FirstName, LastName, Title FROM Person.Person WHERE BusinessEntityID = ?

Использование параметров с ADO.NET и диспетчерами соединений ADO

Диспетчеры соединений ADO.NET и ADO имеют особые требования к командам SQL, использующих параметры.

• Диспетчер соединений ADO.NET требует, чтобы команда SQL использовала имена параметров в качестве маркеров параметров. Это означает, что переменные могут быть прямо сопоставлены с параметрами. Например, переменная @varName сопоставляется с параметром по имени @parName и предоставляет значение параметру @parName.

• Диспетчеры соединений ADO требуют, чтобы в команде SQL в качестве маркеров параметров использовались вопросительные знаки (?). Однако в качестве имен параметров можно использовать любые имена, за исключением целых чисел.

Чтобы предоставить параметрам значения, переменные сопоставляются с именами параметров. Затем задача «Выполнение SQL» использует для загрузки значений из переменных параметры значения порядкового номера имени параметра в списке параметров.

Использование параметров с диспетчерами соединений EXCEL, ODBC и OLE DB

Диспетчеры соединений EXCEL, ODBC и OLE DB требуют, чтобы команды SQL использовали символы знака вопроса (?) в качестве маркеров параметров и числовые значения (начиная с нуля или с единицы) в качестве имен параметров. Если задача «Выполнение SQL» использует диспетчер соединений ODBC, то именем параметра, сопоставляемым первому параметру в запросе, является 1. В противном случае именем параметра будет 0. Для последующих параметров числовое значение имени параметра указывает на параметр в команде SQL, которому сопоставлено это имя параметра. Например, параметр под именем 3 сопоставлен с третьим параметром, который представляется третьим знаком вопроса (?) в команде SQL.

Чтобы предоставить параметрам значения, переменные сопоставляются с именами параметров и задача «Выполнение SQL» использует порядковое значение имени параметра для загрузки значения из переменных в параметры.

В зависимости от поставщика, который используют диспетчеры соединений, некоторые типы данных OLE DB могут не поддерживаться. Например, драйвер Excel распознает только ограниченный набор типов данных. Дополнительные сведения о поведении поставщика Jet с драйвером Excel см. в разделе Источник Excel.

Использование параметров с диспетчерами соединений OLE DB

Если в задаче «Выполнение SQL» используется диспетчер соединений OLE DB, становится доступным свойство BypassPrepare задачи. Этому свойству необходимо присвоить значение true, если задача «Выполнение SQL» использует инструкции SQL с параметрами.

При использовании диспетчера соединений OLE DB нельзя применять параметризованные вложенные запросы, поскольку в задаче «Выполнение SQL» нельзя получить путем анализа информацию о параметрах через поставщик OLE DB. Однако можно использовать выражение, чтобы объединить значения параметров в строку запроса и задать свойство SqlStatementSource этой задачи.

Использование параметров с типами данных даты и времени

Использование параметров даты и времени с ADO.NET и диспетчерами соединений ADO

При считывании данных типов SQL Servertime и datetimeoffset задача «Выполнение SQL», которая использует диспетчер соединений ADO.NET или ADO, имеет следующие дополнительные требования.

• Для данных типа time диспетчер соединений ADO.NET требует, чтобы они хранились в параметре типа Input или Output, имеющем тип данных string.

• Для данных типа datetimeoffset диспетчер соединений ADO.NET требует, чтобы они хранились в одном из следующих параметров.

  • Параметр типа Input имеет тип данных string.

  • Параметр типа Output или ReturnValue имеет тип данных datetimeoffset, string или datetime2. Если выбран параметр с типом данных string или datetime2, то службы Integration Services преобразуют данные в тип string или datetime2.

• Диспетчер соединений ADO требует, чтобы данные time или datetimeoffset хранились в параметре типа Input или Output, имеющем тип данных adVarWchar.

Дополнительные сведения о типах данных SQL Server и их соответствии типам данных служб Integration Services см. в разделах Типы данных (Transact-SQL) и Типы данных служб Integration Services.

Использование параметров даты и времени с диспетчерами соединений OLE DB

При использовании диспетчера соединений OLE DB задача «Выполнение SQL» имеет особые требования по хранению данных типа данных SQL Server: date, time, datetime, datetime2 и datetimeoffset. Эти данные необходимо хранить в параметре одного из следующих типов.

• Входной параметр типа данных NVARCHAR.

• Выходной параметр соответствующего типа данных, как показано в следующей таблице.

  Тип параметра Output	Тип данных даты
  DBDATE	date
  DBTIME2	time
  DBTIMESTAMP	datetime, datetime2
  DBTIMESTAMPOFFSET	datetimeoffset

Если данные не хранятся в соответствующем входном или выходном параметре, выполнение пакета завершается с ошибкой.

Использование параметров даты и времени с диспетчерами соединений ODBC

При использовании диспетчера соединений ODBC задача «Выполнение SQL» имеет особые требования по хранению данных одного из следующих типов данных SQL Server: date, time, datetime, datetime2 и datetimeoffset. Эти данные необходимо хранить в параметре одного из следующих типов.

• Входной параметр input типа данных SQL_WVARCHAR.

• Выходной параметр output соответствующего типа данных, как показано в следующей таблице.

  Тип параметра Output	Тип данных даты
  SQL_DATE	date
  SQL_SS_TIME2	time
  SQL_TYPE_TIMESTAMP или SQL_TIMESTAMP	datetime, datetime2
  SQL_SS_TIMESTAMPOFFSET	datetimeoffset

Если данные не хранятся в соответствующем входном или выходном параметре, выполнение пакета завершается с ошибкой.

Использование параметров в предложениях WHERE

Команды SELECT, INSERT, UPDATE и DELETE часто включают в себя предложение WHERE для задания фильтров, которые определяют условия, которым должна удовлетворять каждая строка в исходной таблице, чтобы попасть под действие команды SQL. Параметры предоставляют значения фильтра в предложениях WHERE.

Можно использовать маркеры параметров для динамического предоставления значений параметрам. Правила для каждого маркера параметра и имени параметра, которые могут быть использованы в инструкции SQL, зависят от типа диспетчера соединений, который используется задачей «Выполнение SQL».

В следующей таблице приведен список примеров команды SELECT для разных типов диспетчеров соединений. Те же самые правила относятся и к инструкциям INSERT, UPDATE и DELETE. В примерах инструкции SELECT возвращают из таблицы Product базы данных База данных AdventureWorks2008R2 продукты, для которых значение ProductID больше и меньше значений, указанных двумя параметрами.

Тип соединений	Синтаксис SELECT
EXCEL, ODBC и OLEDB	SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?
ADO	SELECT* FROM Production.Product WHERE ProductId > ? AND ProductID < ?
ADO.NET	SELECT* FROM Production.Product WHERE ProductId > @parmMinProductID AND ProductID < @parmMaxProductID

Для примеров необходимы параметры со следующими именами.

• Диспетчеры соединений EXCEL и OLED DB используют параметры с именами 0 и 1. Для типа соединения ODBC понадобятся параметры с именами 1 и 2.

• Для типа соединения ADO можно использовать любые два имени параметра, такие как Param1 и Param2, но эти параметры должны быть сопоставлены со своими порядковыми номерами в списке параметров.

• Для типа соединения ADO.NET используются имена параметров @parmMinProductID и @parmMaxProductID.

Использование параметров с хранимыми процедурами

В командах SQL, выполняющих хранимые процедуры, тоже может использоваться сопоставление параметров. Правила использования маркеров и имен параметров зависят от типа диспетчера соединений, который используется задачей «Выполнение SQL», точно так же, как и правила для параметризованных запросов.

В следующей таблице приведен список примеров команды EXEC для разных типов диспетчеров соединений. Примеры выполняют хранимую процедуру uspGetBillOfMaterials в базе данных База данных AdventureWorks2008R2. Хранимая процедура использует параметры @StartProductID и @CheckDateinput.

Тип соединений	Синтаксис EXEC
EXCEL и OLEDB	EXEC uspGetBillOfMaterials ?, ?
ODBC	{call uspGetBillOfMaterials(?, ?)} Дополнительные сведения о синтаксисе вызова ODBC см. в разделе Параметры процедур справочника по программированию ODBC в библиотеке MSDN.
ADO	Если свойство IsQueryStoredProcedure имеет значение False, EXEC uspGetBillOfMaterials ?, ?. Если свойство IsQueryStoredProcedure имеет значение True, uspGetBillOfMaterials.
ADO.NET	Если свойство IsQueryStoredProcedure имеет значение False, EXEC uspGetBillOfMaterials @StartProductID, @CheckDate. Если свойство IsQueryStoredProcedure имеет значение True, uspGetBillOfMaterials.

Чтобы использовать выходные параметры, синтаксис требует, чтобы ключевое слово OUTPUT следовало за каждым маркером параметров. Например, следующий синтаксис выходного параметра является верным: EXEC myStoredProcedure ? OUTPUT.

Дополнительные сведения об использовании входных и выходных параметров с хранимыми процедурами Transact-SQL см. в разделах Параметры (компонент Database Engine), Возвращение данных с помощью параметров OUTPUT и EXECUTE (Transact-SQL).

Возвращение значений кодов возврата

Хранимая процедура может возвращать целочисленное значение, называемое кодом возврата, чтобы указать состояние выполнения процедуры. Чтобы реализовать коды возврата в задаче «Выполнение SQL», используйте параметры типа ReturnValue.

В следующей таблице приведен список типов соединений с примерами команды EXEC, которая реализует коды возврата. Все примеры используют входной параметр input. Правила использования маркеров параметров и имен параметров одинаковые для всех типов параметров: Input, Output и ReturnValue.

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

Тип соединений	Синтаксис EXEC
EXCEL и OLEDB	EXEC ? = myStoredProcedure 1
ODBC	{? = call myStoredProcedure(1)} Дополнительные сведения о синтаксисе вызова ODBC см. в разделе Параметры процедур справочника по программированию ODBC в библиотеке MSDN.
ADO	Если свойство IsQueryStoreProcedure имеет значение False, EXEC ? = myStoredProcedure 1. Если свойство IsQueryStoreProcedure имеет значение True, myStoredProcedure.
ADO.NET	Свойство IsQueryStoreProcedure имеет значение True. myStoredProcedure

В описании синтаксиса, показанном в предыдущей таблице, задача «Выполнение SQL» использует для запуска хранимой процедуры тип источника Прямой ввод. Задача «Выполнение SQL» может также пользоваться для выполнения хранимой процедуры типом источника Соединение с файлом. Независимо от того, какой тип источника использует задача «Выполнение SQL» — Прямой ввод или Соединение с файлом, для реализации кода возврата используйте параметр типа ReturnValue. Дополнительные сведения о настройке типа источника для инструкции SQL, выполняемой задачей «Выполнение SQL», см. в разделе Редактор задачи «Выполнение SQL» (страница «Общие»).

Дополнительные сведения об использовании кодов возврата с хранимыми процедурами Transact-SQL см. в разделах Возврат данных с использованием кода возврата и RETURN (Transact-SQL).

Настройка параметров и кодов возврата в задаче «Выполнение SQL»

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

• Редактор задачи «Выполнение SQL» (страница «Сопоставление параметров»)

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

• Как задать свойства задачи или контейнера

Внешние ресурсы

• Запись в блоге, Хранимые процедуры с выходными параметрами, на сайте blogs.msdn.com

• Образец CodePlex, Параметры задачи «Выполнение SQL» и результирующие наборы, на сайте msftisprodsamples.codeplex.com

Будьте в курсе новых возможностей служб Integration Services Чтобы загрузить последнюю документацию, статьи, образцы и видеоматериалы от Майкрософт, а также лучшие решения от участников сообщества, посетите страницу служб Integration Services на сайтах MSDN или TechNet. Посетить страницу «Службы Integration Services» на сайте MSDN Посетить страницу «Службы Integration Services» на сайте TechNet Чтобы получать автоматические уведомления об этих обновлениях, подпишитесь на RSS-каналы, предлагаемые на этой странице.

См. также

Основные понятия

Задача служб SSIS «Выполнение SQL»

Работа с результирующими наборами в задаче «Выполнение SQL»