通过

表值参数和列值的绑定及数据传输

  • 项目

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics Analytics 平台系统（PDW）

表值参数（TVP）与其他参数一样，必须在将参数传递给服务器之前绑定它们。 应用程序将表值参数与绑定其他参数的方式相同：使用 SQLBindParameter 或对 SQLSetDescField 或 SQLSetDescRec 的等效调用。 表值参数的服务器数据类型为 SQL_SS_TABLE。 C 类型可以指定为 SQL_C_DEFAULT 或 SQL_C_BINARY。

在 SQL Server 2008（10.0.x）或更高版本中，仅支持输入表值参数。 因此，任何将SQL_DESC_PARAMETER_TYPE设置为非SQL_PARAM_INPUT的值的任何尝试都返回具有 SQLSTATE = HY105 和消息“参数类型无效”的SQL_ERROR。

可使用属性 SQL_CA_SS_COL_HAS_DEFAULT_VALUE 为整个表值参数列分配默认值。 但是，不能使用 SQLBindParameter StrLen_or_IndPtr中的SQL_DEFAULT_PARAM为单个表值参数列值分配默认值。 表值参数整体不能通过 SQLBindParameter StrLen_or_IndPtr中的SQL_DEFAULT_PARAM设置为默认值。 如果未遵循这些规则，SQLExecute 或 SQLExecDirect 将返回SQL_ERROR。 诊断记录使用 SQLSTATE=07S01 生成，消息“参数 p> 使用默认参数<无效”，其中 <p> 是查询语句中 TVP 的序号。

注意

表值参数没有可以设置的默认值，因为SQL_DEFAULT_PARAM指示没有行。 因此，如果没有行，则没有要绑定的列。

绑定表值参数之后，应用程序随后必须绑定每个表值参数列。 为此，应用程序首先调用 SQLSetStmtAttr，将SQL_SOPT_SS_PARAM_FOCUS设置为表值参数的序号。 应用程序通过调用以下例程绑定表值参数的列：SQLBindParameter、SQLSetDescRec 和 SQLSetDescField。 将SQL_SOPT_SS_PARAM_FOCUS设置为 0 可还原 SQLBindParameter、SQLSetDescRec 和 SQLSetDescField 在常规顶级参数上运行的常规效果。

注意

对于具有 unixODBC 2.3.1 到 2.3.4 的 Linux 和 Mac ODBC 驱动程序，在使用SQL_CA_SS_TYPE_NAME描述符字段通过 SQLSetDescField 设置 TVP 名称时，unixODBC 不会根据调用的确切函数（SQLSetDescFieldA/SQLSetDescFieldW）自动在 ANSI 和 Unicode 字符串之间转换。 必须始终使用 SQLBindParameter 或 SQLSetDescFieldW 和 Unicode （UTF-16） 字符串来设置 TVP 名称。

对于表值参数本身而言，并未发送或接收实际数据，但对于表值参数的每个构成列而言，发送和接收了数据。 由于表值参数是伪列，因此 SQLBindParameter 的参数引用了不同于其他数据类型的属性，如下所示：

参数 非表值参数类型的相关属性，包括列 表值参数的相关属性
InputOutputType IPD 中的 SQL_DESC_PARAMETER_TYPE。

对于表值参数列，此属性设置必须与表值参数自身的设置相同。		 IPD 中的 SQL_DESC_PARAMETER_TYPE。

此属性必须为 SQL_PARAM_INPUT。
ValueType APD 中的 SQL_DESC_TYPE、SQL_DESC_CONCISE_TYPE。 APD 中的 SQL_DESC_TYPE、SQL_DESC_CONCISE_TYPE。

此属性必须为 SQL_C_DEFAULT 或 SQL_C_BINARY。
ParameterType IPD 中的 SQL_DESC_TYPE、SQL_DESC_CONCISE_TYPE。 IPD 中的 SQL_DESC_TYPE、SQL_DESC_CONCISE_TYPE。

此属性必须为 SQL_SS_TABLE。
ColumnSize IPD 中的 SQL_DESC_LENGTH 或 SQL_DESC_PRECISION。

这取决于 ParameterType 的值。		 SQL_DESC_ARRAY_SIZE

当参数焦点设置为表值参数时，也可以使用 SQL_ATTR_PARAM_SET_SIZE 进行设置。

对于表值参数，此属性为表值参数列缓冲区内的行数。
DecimalDigits IPD 中的 SQL_DESC_PRECISION 或 SQL_DESC_SCALE。 未使用。 此属性必须为 0。

如果此参数不为 0，则 SQLBindParameter 返回SQL_ERROR，并使用 SQLSTATE= HY104 生成诊断记录，消息“精度或小数位数无效”。
ParameterValuePtr APD 中的 SQL_DESC_DATA_PTR。 SQL_CA_SS_TYPE_NAME。

对于存储过程调用，这是可选的，如果不需要，则可以指定 NULL。 必须为不是过程调用的 SQL 语句指定它。

在使用可变行绑定时，此参数还可作为应用程序用于标识该表值参数的唯一值。 有关详细信息，请参阅本主题后面的“可变表值参数行绑定”部分。

在调用 SQLBindParameter 时指定表值参数类型名称时，必须将其指定为 Unicode 值，即使在生成为 ANSI 应用程序的应用程序中也是如此。 用于参数 StrLen_or_IndPtr 的值应为SQL_NTS或名称的字符串长度乘以大小（WCHAR）。
BufferLength APD 中的 SQL_DESC_OCTET_LENGTH。 表值参数类型名称的长度（以字节为单位）。

如果类型名称为 null 终止，则可以SQL_NTS;如果不需要表值参数类型名称，则为 0。
StrLen_or_IndPtr APD 中的 SQL_DESC_OCTET_LENGTH_PTR。 APD 中的 SQL_DESC_OCTET_LENGTH_PTR。

对于表值参数，此属性为行计数，而非数据长度。

表值参数支持两种数据传输模式：固定行绑定和可变行绑定。

固定表值参数行绑定

在固定行绑定模式中，应用程序会分配足以容纳所有可能的输入列值的缓冲区（或缓冲区数组）。 应用程序执行以下操作：

  1. 使用 SQLBindParameter、SQLSetDescRec 或 SQLSetDescField 调用绑定所有参数。

    1. 将 SQL_DESC_ARRAY_SIZE 设置为每个表值参数所能传输的最大行数。 这可以在 SQLBindParameter 调用中完成。

  2. 调用 SQLSetStmtAttr 将SQL_SOPT_SS_PARAM_FOCUS设置为每个表值参数的序号。

    1. 对于每个表值参数，请使用 SQLBindParameter、SQLSetDescRec 或 SQLSetDescField 调用绑定表值参数列。

    2. 对于要具有默认值的每个表值参数列，请调用 SQLSetDescField 将SQL_CA_SS_COL_HAS_DEFAULT_VALUE设置为 1。

  3. 调用 SQLSetStmtAttr 将SQL_SOPT_SS_PARAM_FOCUS设置为 0。 必须在调用 SQLExecute 或 SQLExecDirect 之前执行此操作。 否则，将返回SQL_ERROR，并使用 SQLSTATE=HY024 生成诊断记录，消息“属性值无效，SQL_SOPT_SS_PARAM_FOCUS（执行时必须为零）。

  4. 将 没有 行的表值参数StrLen_or_IndPtr或SQL_DESC_OCTET_LENGTH_PTR设置为SQL_DEFAULT_PARAM，或者在 SQLExecute 或 SQLExecDirect 的下一次调用中传输的行数（如果表值参数具有行）。 不能将表 值参数StrLen_or_IndPtr或SQL_DESC_OCTET_LENGTH_PTR设置为SQL_NULL_DATA，因为表值参数不可为 null（尽管表值参数构成列可以为 null）。 如果此值设置为无效值，则 SQLExecute 或 SQLExecDirect 返回SQL_ERROR，并使用 SQLSTATE=HY090 生成诊断记录，消息“参数 p> 的字符串或缓冲区长度无效”，其中 p 是参数<编号。

  5. 调用 SQLExecute 或 SQLExecDirect。

    如果 StrLen_or_IndPtr设置为列的SQL_LEN_DATA_AT_EXEC （length）或SQL_DATA_AT_EXEC，则可以传入输入表值参数列值。 这类似于使用参数数组时的分块传递值。 与所有数据执行参数一样，SQLParamData 不指示驱动程序请求数据的数组的行;应用程序必须处理此问题。 应用程序无法对驱动程序请求值的顺序做出任何假设。

可变表值参数行绑定

对于可变行绑定，行在执行时分批传输，应用程序按需将行传递到驱动程序。 这类似于单个参数值的执行时数据。 对于可变行绑定，应用程序执行以下操作：

  1. 如上一部分的步骤 1 至 3 中所述，绑定参数和表值参数列：“固定表值参数行绑定”。

  2. 为在执行时传递给SQL_DATA_AT_EXEC的任何表值参数设置 StrLen_or_IndPtr 或SQL_DESC_OCTET_LENGTH_PTR。 如果两者均未设置，则按照上一节中所述处理参数。

  3. 调用 SQLExecute 或 SQLExecDirect。 如果有任何SQL_PARAM_INPUT或SQL_PARAM_INPUT_OUTPUT参数作为数据执行参数进行处理，则返回SQL_NEED_DATA。 在这种情况下，应用程序执行以下操作：

    • 调用 SQLParamData。 这会返回 执行时数据的 ParameterValuePtr 值和SQL_NEED_DATA的返回代码。 当所有参数数据都传递给驱动程序时，SQLParamData 将返回SQL_SUCCESS、SQL_SUCCESS_WITH_INFO或SQL_ERROR。 对于执行时的数据参数， ParameterValuePtr（与描述符字段SQL_DESC_DATA_PTR相同）可以被视为一个标记，用于标识需要值的唯一参数。 此“标记”在绑定时从应用程序传递给驱动程序，在执行时传递回应用程序。

  4. 若要为空表值参数发送表值参数行数据，如果表值参数没有行，应用程序将调用StrLen_or_Ind设置为SQL_DEFAULT_PARAM的 SQLPutData。

    对于非 Null TVP，应用程序会：

    • 将所有表值参数列Str_Len_or_Ind设置为适当的值，并为非执行时数据参数的表值参数列填充数据缓冲区。 可以采用类似于将普通参数分块传递给驱动程序的方式，使用表值参数列的执行时数据。

    • 调用Str_Len_or_Ind设置为要发送到服务器的行数的 SQLPutData。 超出范围 0 到SQL_DESC_ARRAY_SIZE或SQL_DEFAULT_PARAM的任何值都是错误，并返回 SQLSTATE HY090，消息为“字符串或缓冲区长度无效”。0 表示已发送所有行，并且表值参数没有更多数据（如此列表中的第二个项目符号项所述）。 只有在驱动程序第一次请求表值参数数据时，才能使用 SQL_DEFAULT_PARAM（如本列表的第二个项目符号项中所述）。

  5. 发送所有行后，使用 Str_Len_or_Ind 值为 0 的表值参数调用 SQLPutData，然后继续执行上面的步骤 3a。

  6. 再次调用 SQLParamData。 如果表值参数列中存在任何数据执行参数，则这些参数由 SQLParamData 返回的值 ValuePtrPtrPtr 标识。 当所有列值都可用时，SQLParamData 将 返回表值参数的 ParameterValuePtr 值，应用程序将再次开始。

后续步骤

表值参数 ODBC