sp_describe_undeclared_parameters (Transact-SQL)

适用于：sql Server Azure SQL 数据库 Azure SQL 托管实例 Microsoft Fabric Microsoft Fabric Warehouse 中的 Azure Synapse Analytics SQL 分析终结点

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

Transact-SQL 语法约定

语法

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

注意

若要在专用 SQL 池中的 Azure Synapse Analytics 中使用此存储过程，请将数据库兼容性级别设置为 20 或更高。 若要选择退出，请将数据库兼容性级别更改为 10。

参数

[ @tsql = ] 'tsql'

一个或多个 Transact-SQL 语句。 @tsql可能是 nvarchar（n） 或 nvarchar（max）。

[ @params = ] N'params'

@params为 Transact-SQL 批处理的参数提供声明字符串，这与工作方式sp_executesql类似。 @params可能是 nvarchar（n） 或 nvarchar（max）。

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

返回代码值

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

结果集

sp_describe_undeclared_parameters 返回以下结果集。

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

注解

sp_describe_undeclared_parameters 始终返回零的状态。

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

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

sp_describe_undeclared_parameters 在以下任一情况下返回错误。

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

• @params不是NULL，并且包含一个字符串，该字符串不是参数的语法有效的声明字符串，或者它是否包含一个多次声明任何参数的字符串。

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

• 该语句引用临时表。

• 查询包括创建随后要查询的永久表。

如果@tsql没有@params中声明的参数以外的参数，该过程将返回一个空的结果集。

注意

必须将变量声明为标量 Transact-SQL 变量，否则会出现错误。

参数选择算法

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

步骤 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）、过程或方法的参数。
• VALUES语句子句的参数INSERT。
• 或 . 的自变量CASTCONVERT。

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

• 比较或赋值语句的另一侧的数据类型。
• 将此参数传递到的参数的声明数据类型。
• 将该值插入到的列的数据类型。
• 要将语句转换到的数据类型。

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

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

步骤 3：推断数据类型

现在已 E(@p) 识别并 TT(@p) 已识别类型推导算法，可通过以下两种方式之一 @p 推断数据类型：

• 简单推断

  如果E(@p) = @p和TT(@p)存在，即，如果@p直接是步骤 2 开头列出的表达式之一的参数，则类型推导算法将推断出其TT(@p)数据类型@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)。

• 一般推断

  如果简单扣减不适用，则对于未声明的参数，将考虑以下数据类型：

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

  • Money 数据类型（smallmoney、money）

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

  • numeric（38,19） - 不考虑其他数字或十进制数据类型。

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

  • varbinary（8000） 和 varbinary（max） - 不考虑其他二进制数据类型（如 图像、 二进制（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)数据类型，则类型推导算法会将这视为从数据类型到TT(@p)数据类型E(@p)的额外隐式转换。

   例如：

   SELECT * FROM t WHERE Col_Int = Col_Int + @p;
   

   在本例中，E(@p)是Col_Int + @pTT(@p)且为 int。 选择 int 是因为@p它不生成隐式转换。 选择任何其他数据类型都会产生至少一次隐式转换。

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

   SELECT * FROM t WHERE Col_Int = Col_smallint + @p;
   

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

   仅当每个数据类型之间存在根据规则 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时， 将选择 int ， @p 因为它会导致类型 （c） 转换。

权限

需要权限才能执行 @tsql 参数。

示例

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

EXEC 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 参数。

EXEC 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)