sp_describe_undeclared_parameters (Transact-SQL)

返回一个结果集，其中包含有关在 Transact-SQL 批处理中未声明的参数的元数据。请考虑在 @tsql 批处理中使用但未在 @params 中声明的每个参数。每个此类参数在返回的结果集中各占一行，并包含推断的参数类型信息。 如果 **@tsql ** 输入批处理仅包含 @params 中声明的参数，则该过程返回一个空结果集。

Transact-SQL 语法约定

适用范围：SQL Server（SQL Server 2012 至当前版本），Windows Azure SQL Database（初始版本至当前版本）。

语法

sp_describe_undeclared_parameters 
    [ @tsql = ] 'Transact-SQL_batch' 
    [ , [ @params = ] N'parameters' data type ] [, ...n]

参数

• [ @tsql = ] 'Transact-SQL_batch'
  一个或多个 Transact-SQL 语句。 Transact-SQL_batch 可以是 nvarchar(n) 或 nvarchar(max)。

• [ @params = ] N'parameters'
  @params 为 Transact-SQL 批处理参数提供声明字符串，它类似于 sp_executesql。 Parameters 可以是 nvarchar(n) 或 nvarchar(max)。

  一个字符串，它包含 Transact-SQL_batch 中嵌入的所有参数的定义。 字符串必须是 Unicode 常量或 Unicode 变量。 每个参数定义由参数名称和数据类型组成。 n 是表示附加参数定义的占位符。 如果语句中的 Transact-SQL 语句或批处理不包含参数，则不需要使用 @params。 该参数的默认值为 NULL。

• 数据类型
  参数的数据类型。

返回代码值

sp_describe_undeclared_parameters 始终在成功时返回零返回状态。 如果该过程引发错误并且是作为 RPC 调用的，则返回状态将由 sys.dm_exec_describe_first_result_set 的 error_type 列中描述的错误类型填充。 如果该过程是从 Transact-SQL 中调用的，则返回值始终为零，甚至在出现错误情况时也不例外。

结果集

sp_describe_undeclared_parameters 返回以下结果集。

列名	数据类型	说明
parameter_ordinal	int NOT NULL	在结果集中包含参数的序号位置。 第一个参数的位置将指定为 1。
name	sysname NOT NULL	包含参数的名称。
suggested_system_type_id	int NOT NULL	包含在 sys.types 中指定的参数数据类型的 system_type_id。 对于 CLR 类型，即使 system_type_name 列返回 NULL，该列也会返回值 240。
suggested_system_type_name	nvarchar (256) NULL	包含数据类型名称。 包含为参数数据类型指定的参数（例如，length、precision、scale）。 如果数据类型是用户定义的别名类型，则会在此处指定基本系统类型。 如果数据类型是 CLR 用户定义数据类型，则在此列中返回 NULL。 如果无法推断参数类型，则返回 NULL。
suggested_max_length	smallint NOT NULL	请参阅 sys.columns。 了解有关 max_length 列的说明。
suggested_precision	tinyint NOT NULL	请参阅 sys.columns。 了解有关精度列的说明。
suggested_scale	tinyint NOT NULL	请参阅 sys.columns。 了解有关小数位数列的说明。
suggested_user_type_id	int NULL	对于 CLR 和别名类型，包含 sys.types 中指定的列数据类型的 user_type_id。 否则为 NULL。
suggested_user_type_database	sysname NULL	对于 CLR 和别名类型，包含定义类型的数据库的名称。 否则为 NULL。
suggested_user_type_schema	sysname NULL	对于 CLR 和别名类型，包含定义类型的架构的名称。 否则为 NULL。
suggested_user_type_name	sysname NULL	对于 CLR 和别名类型，包含类型的名称。 否则为 NULL。
suggested_assembly_qualified_type_name	nvarchar (4000) NULL	对于 CLR 类型，返回定义类型的程序集和类的名称。 否则为 NULL。
suggested_xml_collection_id	int NULL	包含在 sys.columns 中指定的参数数据类型的 xml_collection_id。 如果返回的类型与 XML 架构集合不关联，该列将返回 NULL。
suggested_xml_collection_database	sysname NULL	包含定义与此类型关联的 XML 架构集合的数据库。 如果返回的类型与 XML 架构集合不关联，该列将返回 NULL。
suggested_xml_collection_schema	sysname NULL	包含定义与此类型关联的 XML 架构集合的架构。 如果返回的类型与 XML 架构集合不关联，该列将返回 NULL。
suggested_xml_collection_name	sysname NULL	包含与此类型关联的 XML 架构集合的名称。 如果返回的类型与 XML 架构集合不关联，该列将返回 NULL。
suggested_is_xml_document	bit NOT NULL	如果要返回的类型为 XML，并且保证该类型是 XML 文档，则返回 1。 否则，返回 0。
suggested_is_case_sensitive	bit NOT NULL	如果列为区分大小写的字符串类型，则返回 1；否则，返回 0。
suggested_is_fixed_length_clr_type	bit NOT NULL	如果列为固定长度 CLR 类型，则返回 1；否则，返回 0。
suggested_is_input	bit NOT NULL	如果在赋值语句左侧以外的任何地方使用参数，则返回 1。 否则，返回 0。
suggested_is_output	bit NOT NULL	如果在赋值语句左侧使用参数或将参数传递给存储过程的输出参数，则返回 1。 否则，返回 0。
formal_parameter_name	sysname NULL	如果参数是存储过程或用户定义的函数的参数，则返回相应形参的名称。 否则，返回 NULL。
suggested_tds_type_id	int NOT NULL	供内部使用。
suggested_tds_length	int NOT NULL	供内部使用。

注释

sp_describe_undeclared_parameters 始终返回零返回状态。

最常见的用途是，为应用程序提供的 Transact-SQL 语句可能包含一些参数，并且必须以某种方式处理这些参数。 一个例子是用户接口（如 ODBCTest 或 RowsetViewer），用户可以在其中使用 ODBC 参数语法提供查询。 应用程序必须动态查找参数数目，并提示用户输入每个参数。

另一个例子是，在没有用户输入时，应用程序必须循环访问这些参数，并从某个其他位置（例如，表）获取这些参数的数据。在这种情况下，应用程序不必同时传递所有参数信息。 相反，应用程序可以从提供程序中获取所有参数信息，并从表中获取数据本身。 使用 sp_describe_undeclared_parameters 的代码更通用一些；如果数据结构以后发生变化，也不太可能需要进行修改。

在下面的任何情况下，sp_describe_undeclared_parameters 将返回错误。

• 如果输入 @tsql 不是有效的 Transact-SQL 批处理。 有效性是通过解析和分析 Transact-SQL 批处理来确定的。 当确定 Transact-SQL 批处理是否有效时，将不考虑由批处理在查询优化或执行过程中导致的任何错误。

• 如果 @params 不为 NULL，并且包含的某个字符串在语法上不是有效的参数声明字符串，或者如果它包含一个将任何参数声明多次的字符串。

• 如果输入 Transact-SQL 批处理声明的某个局部变量与在 @params 参数中声明的某个参数同名。

• 如果该语句将创建任何临时表。

如果 @tsql 仅包含 @params 中声明的参数，则该过程返回一个空结果集。

参数选择算法

对于具有未声明的参数的查询，将通过三个步骤推断未声明的参数的数据类型。

步骤 1

要推断具有未声明的参数的查询的数据类型，第一步骤是查找数据类型不依赖于未声明的参数的所有子表达式的数据类型。 可以确定以下表达式的类型：

• 列、常量、变量以及声明的参数。

• 调用用户定义的函数 (UDF) 的结果。

• 数据类型不依赖于所有输入的未声明参数的表达式。

例如，请考虑 SELECT dbo.tbl(@p1) + c1 FROM t1 WHERE c2 = @p2 + 2 查询。 表达式 dbo.tbl(@p1) + c1 和 c2 具有数据类型，而表达式 @ p1 和 @ p2 + 2 没有数据类型。

在执行此步骤后，如果任何表达式（对 UDF 的调用除外）有两个没有数据类型的参数，类型推断将失败并发生错误。 例如，下面的语句均产生错误：

SELECT * FROM t1 WHERE @p1 = @p2
SELECT * FROM t1 WHERE c1 = @p1 + @p2
SELECT * FROM t1 WHERE @p1 = SUBSTRING(@p2, 2, 3)

以下示例不产生错误：

SELECT * FROM t1 WHERE @p1 = dbo.tbl(c1, @p2, @p3)

步骤 2

对于给定的未声明参数 @p，类型推断算法查找包含 @p 并且为下列内容之一的最内部表达式 E(@p)：

• 比较或赋值运算符的参数。

• 用户定义的函数（包括表值 UDF）、过程或方法的参数。

• INSERT 语句的 VALUES 子句的参数。

• CAST 或 CONVERT 的参数。

类型推断算法查找 E(@p) 的目标数据类型 TT(@p)。 上述示例的目标数据类型如下所示：

• 比较或赋值语句的另一侧的数据类型。

• 将此参数传递到的参数的声明数据类型。

• 将该值插入到的列的数据类型。

• 要将语句转换到的数据类型。

例如，请考虑 SELECT * FROM t WHERE @p1 = dbo.tbl(@p2 + c1) 查询。 然后考虑 E(@p1) = @p1、E(@p2) = @p2 + c1，TT(@p1) 是 dbo.tbl 的声明返回数据类型，TT(@p2) 是 dbo.tbl 的声明参数数据类型。

如果在步骤 2 开头列出的任何表达式中不包含 @p，则类型推断算法确定 E(@p) 是包含 @p 的最大标量表达式，并且类型推断算法不会计算 E(@p) 的目标数据类型 TT(@p)。 例如，如果查询为 SELECT @p + 2，则 E(@p) = @p + 2，并且没有 TT(@p)。

步骤 3

现在已确定了 E(@p) 和 TT(@p)，类型推断算法将按以下两种方式之一推断 @p 的数据类型：

• 简单推断

  如果 E(@p) = @p 和 TT(@p) 存在，即，如果 @p 直接是在步骤 2 开头中列出的表达式之一的参数，则推断算法将 @p 的数据类型推断为 TT(@p)。 例如：

  SELECT * FROM t WHERE c1 = @p1 AND @p2 = dbo.tbl(@p3)
  

  @p1、@p2 和 @p3 的数据类型分别为 c1 数据类型、dbo.tbl 返回数据类型以及 dbo.tbl 参数数据类型。

  作为一个特例，如果 @p 是 <、>、<= 或 > = 运算符的参数，则简单推断规则不适用。 类型推断算法使用下一节介绍的一般推断规则。 例如，如果 c1 是 char(30) 数据类型的列，请考虑下面两个查询：

  SELECT * FROM t WHERE c1 = @p
  SELECT * FROM t WHERE c1 > @p
  

  对于第一种情况，类型推断算法根据本主题前面的规则将 char(30) 推断为 @p 数据类型。 对于第二种情况，类型推断算法根据下一节中的一般推断规则推断 varchar(8000)。

• 一般推断

  如果简单推断不适用，则为未声明的参数考虑以下数据类型：

  • 整数数据类型（bit、tinyint、smallint、int、bigint）

  • 货币数据类型（smallmoney、money）

  • 浮点数据类型（float、real）

  • numeric(38, 19) - 不考虑其他数字或小数数据类型。

  • varchar(8000)、varchar(max)、nvarchar(4000) 和 nvarchar(max) - 不考虑其他字符串数据类型（如 text、char(8000)、nvarchar(30) 等）。

  • varbinary(8000) 和 varbinary(max) - 不考虑其他二进制数据类型（如 image、binary(8000)、varbinary(30) 等）。

  • date、time(7)、 smalldatetime、datetime、 datetime2(7)、datetimeoffset(7) - 不考虑其他日期和时间类型（如 time(4)）。

  • sql_variant

  • xml

  • CLR 系统定义的类型（hierarchyid、geometry、geography）

  • CLR 用户定义的类型

选择条件

在候选数据类型中，将拒绝使查询无效的任何数据类型。 在其余候选数据类型中，类型推断算法将根据以下规则选择一种数据类型。

1. 选择在 E(@p) 中产生隐式转换次数最少的数据类型。 如果特定数据类型生成的 E(@p) 数据类型不同于 TT(@p)，则类型推断算法将其视为从数据类型 E(@p) 到 TT(@p) 的额外隐式转换。

   例如：

   SELECT * FROM t WHERE Col_Int = Col_Int + @p
   

   在这种情况下，E(@p) 为 Col_Int + @p，TT(@p) 为 int。 将为 @p 选择 int，因为它不产生隐式转换。 选择任何其他数据类型都会产生至少一次隐式转换。

2. 如果多种数据类型都产生次数最少的转换，则使用具有较高优先级的数据类型。 例如：

   SELECT * FROM t WHERE Col_Int = Col_smallint + @p
   

   在这种情况下，int 和 smallint 产生一次转换。 每种其他数据类型产生多次转换。 由于 int 优先于 smallint，因此，@p 将使用 int。 有关数据类型优先级的详细信息，请参阅数据类型优先级 (Transact-SQL)。

   只有在每种数据类型之间的隐式转换次数相同（按照规则 1）并且某种数据类型具有最高优先级时，此规则才适用。 如果没有隐式转换，数据类型推断将失败并发生错误。 例如，在 SELECT @p FROM t 查询中，由于 @p 的任何数据类型同样良好，数据类型推断失败。 例如，没有从 int 到 xml 的隐式转换。

3. 如果两种类似的数据类型按照规则 1 等效，例如， varchar(8000) 和 varchar(max)，则选择较小的数据类型 (varchar(8000))。 这一原则也适用于 nvarchar 和 varbinary 数据类型。

4. 就规则 1 而言，类型推断算法倾向于将某些转换视为比其他转换好。 转换从最好到最坏依次为：

   1. 不同长度的相同基本数据类型之间的转换。

   2. 相同数据类型的固定长度和可变长度版本之间的转换（如 char 到 varchar）。

   3. NULL 和 int 之间的转换。

   4. 任何其他转换。

例如，对于 SELECT * FROM t WHERE [Col_varchar(30)] > @p 查询，将选择 varchar(8000)，因为转换 (a) 是最好的。 对于 SELECT * FROM t WHERE [Col_char(30)] > @p 查询，仍选择 varchar(8000)，因为它产生类型 (b) 转换，而另一种选择（如 varchar(4000)）产生类型 (d) 转换。

作为最后一个示例，请考虑 SELECT NULL + @p 查询，将为 @p 选择 int，因为它产生类型 (c) 转换。

权限

要求具有执行 @tsql 参数的权限。

示例

以下示例返回一些信息，例如，未声明的 @id 和 @name 参数的预期数据类型。

sp_describe_undeclared_parameters @tsql = 
N'SELECT object_id, name, type_desc 
FROM sys.indexes
WHERE object_id = @id OR name = @name'

如果 @id 参数是作为 @params 引用提供的，则会在结果集中省略 @id 参数，而仅描述 @name 参数。

sp_describe_undeclared_parameters @tsql = 
N'SELECT object_id, name, type_desc 
FROM sys.indexes
WHERE object_id = @id OR NAME = @name',
@params = N'@id int'

请参阅

参考

sp_describe_first_result_set (Transact-SQL)

sys.dm_exec_describe_first_result_set (Transact-SQL)

sys.dm_exec_describe_first_result_set_for_object (Transact-SQL)