执行 SQL 任务中的参数和返回代码

SQL 语句和存储过程常常使用 input 参数、output 参数和返回代码。 在 Integration Services 中，执行 SQL 任务支持 Input、Output 和 ReturnValue 参数类型。 Input 类型用于输入参数，Output 用于输出参数，ReturnValue 用于返回代码。

注意
只有数据访问接口支持这些参数时，才可在执行 SQL 任务中使用它们。

SQL 命令（包括查询和存储过程）中的参数被映射到在执行 SQL 任务作用域、父容器或包的作用域内创建的用户定义变量。 变量的值可在设计时设置，也可在运行时动态填充。 还可以将参数映射到系统变量。 有关详细信息，请参阅 Integration Services (SSIS) 变量和系统变量。

但是，在执行 SQL 任务中使用参数和返回代码不只是要了解该任务支持的参数类型以及如何映射这些参数。 还有其他使用要求和准则可帮助您在执行 SQL 任务中成功使用参数和返回代码。 本主题的其余部分将介绍这些使用要求和准则：

• 使用参数名称和标记

• 将参数用于日期和时间数据类型

• 在 WHERE 子句中使用参数

• 在存储过程中使用参数

• 获取返回代码的值

• 在执行 SQL 任务编辑器中配置参数和返回代码

使用参数名称和标记

执行 SQL 任务使用不同的连接类型时，SQL 命令的语法使用不同的参数标记。 例如，ADO.NET 连接管理器类型要求 SQL 命令使用格式为 @varParameter 的参数标记，而 OLE DB 连接类型要求使用问号 (?) 参数标记。

在变量与参数之间的映射中可以用作参数名的名称也因连接管理器类型而异。 例如，ADO.NET 连接管理器类型使用带 @ 前缀的用户定义名称，而 OLE DB 连接管理器类型要求使用从 0 开始的序数数值作为参数名。

下表总结了执行 SQL 任务可以使用的连接管理器类型的 SQL 命令要求。

在 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 命令使用问号 (?) 作为参数标记，并且使用从 0 开始或从 1 开始的数值作为参数名称。 如果执行 SQL 任务使用 ODBC 连接管理器，则映射到查询中的第一个参数的参数名称将为 1；否则该参数将命名为 0。 对于后续参数，参数名称的数值指示参数名称在 SQL 命令中映射到的参数。 例如，名为 3 的参数映射到第三个参数，这是由 SQL 命令中的第三个问号 (?) 来表示的。

若要向参数提供值，可以将变量映射到参数名称，然后执行 SQL 任务使用参数名称的序数值将值从变量加载到参数。

连接管理器使用的访问接口不同时，某些 OLE DB 数据类型可能不受支持。 例如，Excel 驱动程序只识别有限的一组数据类型。 有关带有 Excel 驱动程序的 Jet 访问接口的行为的详细信息，请参阅 Excel 源。

在 OLE DB 连接管理器中使用参数

如果执行 SQL 任务使用 OLE DB 连接管理器，则该任务的 BypassPrepare 属性可用。 如果执行 SQL 任务使用带有参数的 SQL 语句，则应将此属性设置为 true。

使用 OLE DB 连接管理器时，不能使用参数化的子查询，这是因为执行 SQL 任务不能通过 OLE DB 访问接口得到参数信息。 但是，您可以使用表达式将参数值串联到查询字符串中，并设置该任务的 SqlStatementSource 属性。

将参数用于日期和时间数据类型

在 ADO.NET 和 ADO 连接管理器中使用日期和时间参数

读取 SQL Server 类型 time 和 datetimeoffset 的数据时，使用 ADO.NET 或 ADO 连接管理器的执行 SQL 任务有以下附加要求：

• 对于 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 参数类型	Date 数据类型
  DBDATE	date
  DBTIME2	time
  DBTIMESTAMP	datetime,datetime2
  DBTIMESTAMPOFFSET	datetimeoffset

如果数据未存储在相应的输入或输出参数中，包将失败。

在 ODBC 连接管理器中使用日期和时间参数

使用 ODBC 连接管理器时，执行 SQL 任务对于带有以下 SQL Server 数据类型之一的数据具有特定的存储要求：date、time、datetime、datetime2 或 datetimeoffset。 您必须用下列参数类型之一来存储此数据：

• SQL_WVARCHAR 数据类型的 input 参数

• 具有适当数据类型的 output 参数，如下表中所示。

  Output 参数类型	Date 数据类型
  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 返回 AdventureWorks2012 的 Product 表中 ProductID 大于且小于由两个参数指定的值的产品。

这些示例要求使用具有以下名称的参数：

• EXCEL 和 OLED DB 连接管理器使用参数名称 0 和 1。 ODBC 连接类型使用 1 和 2。

• ADO 连接类型可以使用任何两个参数名称，例如 Param1 和 Param2，但是这两个参数必须按其在参数列表中的序数位置进行映射。

• ADO.NET 连接类型使用参数名称 @parmMinProductID 和 @parmMaxProductID。

在存储过程中使用参数

运行存储过程的 SQL 命令也可以使用参数映射。 与参数化查询的规则一样，参数标记和参数名称的使用规则取决于执行 SQL 所使用的连接管理器的类型。

下表按连接管理器类型列出了 EXEC 命令的示例。 示例运行 AdventureWorks2012 中的 uspGetBillOfMaterials 存储过程。 该存储过程使用 @StartProductID 和 @CheckDate input 参数。

若要使用输出参数，则语法要求在每个参数标记后跟 OUTPUT 关键字。 例如，以下 output 参数语法是正确的：EXEC myStoredProcedure ? OUTPUT。

有关在 Transact-SQL 存储过程中使用输入和输出参数的详细信息，请参阅 EXECUTE (Transact-SQL)。

获取返回代码的值

存储过程可以返回一个整数值（称为“返回代码”），以指示过程的执行状态。 若要在执行 SQL 任务中实现返回代码，需要使用 ReturnValue 类型的参数。

下表按连接类型列出了实现返回代码的某些 EXEC 命令示例。 所有示例均使用 input 参数。 对于所有参数类型（Input、Output 和 ReturnValue），参数标记和参数名称的使用规则都相同。

某些语法不支持参数文字。 在此情况下，必须通过使用变量来提供参数值。

如上表中语法所示，执行 SQL 任务使用**“直接输入”源类型来运行存储过程。 执行 SQL 任务还可以使用“文件连接”源类型来运行存储过程。 无论执行 SQL 任务是使用“直接输入”源类型还是使用“文件连接”**源类型，都请使用 ReturnValue 类型的参数来实现返回代码。 有关如何配置执行 SQL 任务运行的 SQL 语句的源类型的详细信息，请参阅执行 SQL 任务编辑器（“常规”页）。

有关在 Transact-SQL 存储过程中使用返回代码的详细信息，请参阅 RETURN (Transact-SQL)。

在执行 SQL 任务中配置参数和返回代码

有关可以在 SSIS 设计器中设置的参数和返回代码的属性的详细信息，请单击以下主题：

• 执行 SQL 任务编辑器（“参数映射”页）

有关如何在 SSIS 设计器中设置这些属性的详细信息，请单击下列主题：

• 设置任务或容器的属性

相关任务

设置任务或容器的属性

相关内容

• blogs.msdn.com 上的博客项 Stored procedures with output parameters（无输出参数的存储过程）

• msftisprodsamples.codeplex.com 上的 CodePlex 示例 Execute SQL Parameters and Result Sets（执行 SQL 参数和结果集）

请参阅

概念

执行 SQL 任务

执行 SQL 任务中的结果集