通过

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

  • 项目

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse AnalyticsAnalytics Platform System (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 来分配单个表值参数列值。 不能通过将 StrLen_or_IndPtr 中的 SQL_DEFAULT_PARAM 与 SQLBindParameter 结合使用来将 表 值参数作为一个整体设置为默认值。 如果不遵循这些规则，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。 如果此值设置为无效值，则 SQLExecute 或 SQLExecDirect 将返回SQL_ERROR，并生成 SQLSTATE=HY090 和消息“参数 <p> 的字符串或缓冲区长度无效”，其中 p 是参数编号。

  5. 调用 SQLExecute 或 SQLExecDirect。

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

可变表值参数行绑定

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

  1. 如上一节“固定Table-Valued参数行绑定”的步骤 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. 若要为 null 表值参数发送表值参数行数据，如果表值参数没有行，应用程序会调用 SQLPutData， StrLen_or_Ind 设置为 SQL_DEFAULT_PARAM。

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

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

    • 调用 SQLPutData， Str_Len_or_Ind 设置为要发送到服务器的行数。 超出范围 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 返回的值 ValuePtrPtr 标识。 当所有列值都可用时，SQLParamData 将返回表值参数的 ParameterValuePtr 值，应用程序将重新开始。

后续步骤

表值参数 ODBC