• Статья

Работа с параметрами и коды возврата в задаче «Выполнение 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.Contact WHERE ContactID = ?

ADO.NET

@<имя параметра>

@<имя параметра>

SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = @parmContactID

ODBC

?

1, 2, 3, …

SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?

EXCEL и OLE DB

?

0, 1, 2, 3, …

SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?

Использование параметров с 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 базы данных AdventureWorks продукты, для которых значение 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 в базе данных AdventureWorks. Хранимая процедура использует параметры @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, см. в следующем разделе:

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

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

Значок служб Integration Services (маленький) Будьте в курсе новых возможностей cлужб Integration Services

Чтобы загрузить новейшую документацию, статьи, образцы и видеоматериалы от корпорации Майкрософт, а также лучшие решения от участников сообщества, посетите страницу Integration Services на сайтах MSDN или TechNet:

Чтобы получать автоматические уведомления об этих обновлениях, подпишитесь на RSS-каналы, предлагаемые на этой странице.