sp_describe_undeclared_parameters (Transact-SQL)sp_describe_undeclared_parameters (Transact-SQL)

適用於: 是SQL Server 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

傳回結果集,其中包含有關 Transact-SQLTransact-SQL 批次中未宣告之參數的中繼資料。Returns a result set that contains metadata about undeclared parameters in a Transact-SQLTransact-SQL batch. 會考慮 @tsql批次中使用的每個參數,但不會在 @params中宣告。Considers each parameter that is used in the @tsql batch, but not declared in @params. 傳回的結果集中,針對每一個這類參數包含一個資料列,內含該參數的推算類型資訊。A result set is returned that contains one row for each such parameter, with the deduced type information for that parameter. 如果 @tsql輸入批次沒有參數(在 @params中宣告),則此程式會傳回空的結果集。The procedure returns an empty result set if the @tsql input batch has no parameters except those declared in @params.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

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

引數Arguments

[ \@tsql = ] 'Transact-SQL\_batch' 一個或多個 Transact-SQLTransact-SQL 語句。[ \@tsql = ] 'Transact-SQL\_batch' One or more Transact-SQLTransact-SQL statements. 交易 SQL_batch可以是Nvarchar ( n Nvarchar (max)Transact-SQL_batch may be nvarchar(n) or nvarchar(max).

[ \@params = ] N'parameters' @params 會提供 Transact-SQLTransact-SQL 批次參數的宣告字串,類似于 sp_executesql 的運作方式。[ \@params = ] N'parameters' @params provides a declaration string for parameters for the Transact-SQLTransact-SQL batch, similarly to the way sp_executesql works. 參數可以是Nvarchar ( n Nvarchar (max)Parameters may be nvarchar(n) or nvarchar(max).

這是一個字串,其中包含已內嵌在交易 SQL_batch中的所有參數的定義。Is one string that contains the definitions of all parameters that have been embedded in Transact-SQL_batch. 此字串必須是 Unicode 常數或 Unicode 變數。The string must be either a Unicode constant or a Unicode variable. 每個參數定義都由參數名稱和資料類型組成。Each parameter definition consists of a parameter name and a data type. n 是指出其他參數定義的預留位置。n is a placeholder that indicates additional parameter definitions. 如果語句中的 Transact-sql 語句或批次不包含參數,則不需要 @params。If the Transact-SQL statement or batch in the statement does not contain parameters, @params is not required. 這個參數的預設值是 NULL。The default value for this parameter is NULL.

資料類型Datatype
參數的資料類型。The data type of the parameter.

傳回碼值Return Code Values

sp_describe_undeclared_parameters一律會在成功時傳回傳回狀態0。sp_describe_undeclared_parameters always returns return status of zero on success. 如果程式擲回錯誤,並將程式稱為 RPC,則傳回狀態會填入 dm_exec_describe_first_result_set 的 error_type 資料行中所述的錯誤類型。If the procedure throws an error and the procedure is called as an RPC, the return status is populated by the type of error as described in the error_type column of sys.dm_exec_describe_first_result_set. 如果程序是從 Transact-SQLTransact-SQL 所呼叫,即使發生錯誤,傳回值也永遠是零。If the procedure is called from Transact-SQLTransact-SQL, the return value is always zero, even in error cases.

結果集Result Sets

sp_describe_undeclared_parameters會傳回下列結果集。sp_describe_undeclared_parameters returns the following result set.

資料行名稱Column name [名稱]Data type 描述Description
parameter_ordinalparameter_ordinal int NOT Nullint NOT NULL 包含結果集中的參數序數位置。Contains the ordinal position of the parameter in the result set. 第一個參數的位置將會指定為 1。Position of the first parameter will be specified as 1.
namename sysname 不是 Nullsysname NOT NULL 包含參數的名稱。Contains the name of the parameter.
suggested_system_type_idsuggested_system_type_id int NOT Nullint NOT NULL 包含在 sys.databases 中指定之參數資料類型的system_type_idContains the system_type_id of the data type of the parameter as specified in sys.types.

針對 CLR 類型,即使system_type_name資料行會傳回 Null,這個資料行會傳回值240。For CLR types, even though the system_type_name column will return NULL, this column will return the value 240.
suggested_system_type_namesuggested_system_type_name Nvarchar (256) Nullnvarchar (256) NULL 包含資料類型名稱。Contains the data type name. 包含指定給參數之資料類型的引數 (例如長度、有效位數、小數位數)。Includes arguments (such as length, precision, scale) specified for the data type of the parameter. 如果資料類型是使用者定義的別名類型,這裡就會指定基礎系統類型。If the data type is a user-defined alias type, the underlying system type is specified here. 如果它是 CLR 使用者定義資料類型,這個資料行就會傳回 NULL。If it is a CLR user-defined data type, NULL is returned in this column. 如果無法推算參數的類型,則傳回 NULL。If the type of the parameter cannot be deduced, NULL is returned.
suggested_max_lengthsuggested_max_length Smallint NOT Nullsmallint NOT NULL 請參閱 sys.databases。See sys.columns. max_length資料行描述。for max_length column description.
suggested_precisionsuggested_precision Tinyint NOT Nulltinyint NOT NULL 請參閱 sys.databases。See sys.columns. 以查看 precision 資料行的說明。for precision column description.
suggested_scalesuggested_scale Tinyint NOT Nulltinyint NOT NULL 請參閱 sys.databases。See sys.columns. 以查看 scale 資料行的說明。for scale column description.
suggested_user_type_idsuggested_user_type_id int Nullint NULL 針對 CLR 和別名類型,會如同 sys.types 中所指定,包含資料行資料類型的 user_type_id。For CLR and alias types, contains the user_type_id of the data type of the column as specified in sys.types. 否則,便為 NULL。Otherwise is NULL.
suggested_user_type_databasesuggested_user_type_database sysname Nullsysname NULL 針對 CLR 和別名類型,會包含定義類型之資料庫的名稱。For CLR and alias types, contains the name of the database in which the type is defined. 否則,便為 NULL。Otherwise is NULL.
suggested_user_type_schemasuggested_user_type_schema sysname Nullsysname NULL 針對 CLR 和別名類型,會包含定義類型之結構描述的名稱。For CLR and alias types, contains the name of the schema in which the type is defined. 否則,便為 NULL。Otherwise is NULL.
suggested_user_type_namesuggested_user_type_name sysname Nullsysname NULL 針對 CLR 和別名類型,會包含類型的名稱。For CLR and alias types, contains the name of the type. 否則,便為 NULL。Otherwise is NULL.
suggested_assembly_qualified_type_namesuggested_assembly_qualified_type_name Nvarchar (4000) Nullnvarchar (4000) NULL 針對 CLR 類型,會傳回定義類型之組件與類別的名稱。For CLR types, returns the name of the assembly and class that defines the type. 否則,便為 NULL。Otherwise is NULL.
suggested_xml_collection_idsuggested_xml_collection_id int Nullint NULL 包含在 sys.databases 中指定之參數的資料類型 xml_collection_id。Contains the xml_collection_id of the data type of the parameter as specified in sys.columns. 如果傳回的類型沒有與 XML 結構描述集合相關聯,這個資料行將傳回 NULL。This column will return NULL if the type returned is not associated with an XML schema collection.
suggested_xml_collection_databasesuggested_xml_collection_database sysname Nullsysname NULL 包含定義與這個類型相關聯之 XML 結構描述集合的資料庫。Contains the database in which the XML schema collection associated with this type is defined. 如果傳回的類型沒有與 XML 結構描述集合相關聯,這個資料行將傳回 NULL。This column will return NULL if the type returned is not associated with an XML schema collection.
suggested_xml_collection_schemasuggested_xml_collection_schema sysname Nullsysname NULL 包含定義與這個類型相關聯之 XML 結構描述集合的結構描述。Contains the schema in which the XML schema collection associated with this type is defined. 如果傳回的類型沒有與 XML 結構描述集合相關聯,這個資料行將傳回 NULL。This column will return NULL if the type returned is not associated with an XML schema collection.
suggested_xml_collection_namesuggested_xml_collection_name sysname Nullsysname NULL 包含與這個類型相關聯之 XML 結構描述集合的名稱。Contains the name of the XML schema collection associated with this type. 如果傳回的類型沒有與 XML 結構描述集合相關聯,這個資料行將傳回 NULL。This column will return NULL if the type returned is not associated with an XML schema collection.
suggested_is_xml_documentsuggested_is_xml_document 位 NOT Nullbit NOT NULL 如果正要傳回的類型是 XML,而且該類型保證是 XML 文件,則傳回 1。Returns 1 if the type being returned is XML and that type is guaranteed to be an XML document. 否則傳回 0。Otherwise returns 0.
suggested_is_case_sensitivesuggested_is_case_sensitive 位 NOT Nullbit NOT NULL 如果資料行是區分大小寫的字串類型,則傳回 1,否則傳回 0。Returns 1 if the column is of a case-sensitive string type and 0 if it is not.
suggested_is_fixed_length_clr_typesuggested_is_fixed_length_clr_type 位 NOT Nullbit NOT NULL 如果資料行是固定長度的 CLR 類型,則傳回 1,否則傳回 0。Returns 1 if the column is of a fixed-length CLR type and 0 if it is not.
suggested_is_inputsuggested_is_input 位 NOT Nullbit NOT NULL 如果參數用於指派左邊以外的任何地方,則傳回 1。Returns 1 if the parameter is used anywhere other than left side of an assignment. 否則傳回 0。Otherwise returns 0.
suggested_is_outputsuggested_is_output 位 NOT Nullbit NOT NULL 如果參數用在指派的左邊,或傳遞至預存程序的輸出參數,則傳回 1。Returns 1 if the parameter is used on the left side of an assignment or is passed to an output parameter of a stored procedure. 否則傳回 0。Otherwise returns 0.
formal_parameter_nameformal_parameter_name sysname Nullsysname NULL 如果參數是預存程序或使用者定義函數的引數,則傳回對應型式參數的名稱。If the parameter is an argument to a stored procedure or a user-defined function, returns the name of the corresponding formal parameter. 否則,便傳回 NULL。Otherwise returns NULL.
suggested_tds_type_idsuggested_tds_type_id int NOT Nullint NOT NULL 供內部使用。For internal use.
suggested_tds_lengthsuggested_tds_length int NOT Nullint NOT NULL 供內部使用。For internal use.

RemarksRemarks

sp_describe_undeclared_parameters一律會傳回傳回的狀態為零。sp_describe_undeclared_parameters always returns return status of zero.

最常見的用法是在應用程式中提供可能包含參數且必須以特定方式處理的 Transact-SQLTransact-SQL 陳述式。The most common use is when an application is given a Transact-SQLTransact-SQL statement that might contain parameters and must process them in some way. 其中一個範例是使用者介面(例如 ODBCTest 或 RowsetViewer),使用者可在其中提供具有 ODBC 參數語法的查詢。An example is a user interface (such as ODBCTest or RowsetViewer) where the user provides a query with ODBC parameter syntax. 應用程式必須動態探索參數數目,並提示使用者提供每個參數值。The application must dynamically discover the number of parameters and prompt the user for each one.

另一個例子是,當沒有使用者輸入的情況下,應用程式必須在參數上執行迴圈,並從其他位置 (例如資料表) 為它們取得資料。Another example is when without user input, an application must loop over the parameters and obtain the data for them from some other location (such as a table). 在這種情況下,應用程式不必一次傳遞所有參數資訊。In this case, the application does not have to pass all the parameter information at once. 相反地,應用程式可以從提供者取得所有參數資訊,並自行從資料表取得資料。Instead, the application can get all the parameters information from the provider and obtain the data itself from the table. 使用sp_describe_undeclared_parameters的程式碼較為通用,如果資料結構之後變更,則較不可能需要修改。Code using sp_describe_undeclared_parameters is more generic and is less likely to require modification if the data structure changes later.

sp_describe_undeclared_parameters在下列任何情況下都會傳回錯誤。sp_describe_undeclared_parameters returns an error in any of the following cases.

  • 如果輸入 @tsql 不是有效的 Transact-SQLTransact-SQL 批次。If the input @tsql is not a valid Transact-SQLTransact-SQL batch. 有效性是藉由剖析和分析 Transact-SQLTransact-SQL 批次來判斷。Validity is determined by parsing and analyzing the Transact-SQLTransact-SQL batch. 判斷 Transact-SQLTransact-SQL 批次是否有效時,批次在查詢優化期間或執行期間所造成的任何錯誤都不會列入考慮。Any errors caused by the batch during query optimization or during execution are not considered when determining whether the Transact-SQLTransact-SQL batch is valid.

  • 如果 @params 不是 Null,而且包含的字串不是以語法表示的有效宣告字串,或其包含的字串會宣告任何參數一次以上,則為。If @params is not NULL and contains a string that is not a syntactically valid declaration string for parameters, or if it contains a string that declares any parameter more than one time.

  • 如果輸入 Transact-SQLTransact-SQL 批次宣告的區域變數名稱與 @params 中所宣告的參數相同。If the input Transact-SQLTransact-SQL batch declares a local variable of the same name as a parameter declared in @params.

  • 如果語句參考臨時表,則為。If the statement references temporary tables.

  • 查詢包含建立隨後要查詢的永久資料表。The query includes the creation of a permanent table that is then queried.

如果 @tsql 沒有參數(而不是在 @params 中宣告),則程式會傳回空的結果集。If @tsql has no parameters, other than those declared in @params, the procedure returns an empty result set.

參數選取演算法Parameter Selection Algorithm

針對具有未宣告之參數的查詢,在三個步驟中進行未宣告之參數的資料類型推算。For a query with undeclared parameters, data type deduction for undeclared parameters proceeds in three steps.

步驟1Step 1

針對具有未宣告之參數的查詢,資料類型推算的第一步是找出資料類型不相依於未宣告的參數之所有子運算式的資料類型。The first step in data type deduction for a query with undeclared parameters is to find the data types of all the sub-expressions whose data types do not depend on the undeclared parameters. 可判斷下列運算式的類型:The type can be determined for the following expressions:

  • 資料行、常數、變數和宣告的參數。Columns, constants, variables, and declared parameters.

  • 使用者定義函數 (UDF) 呼叫的結果。Results of a call to a user-defined function (UDF).

  • 資料類型不相依於所有輸入中未宣告的參數之運算式。An expression with data types that do not depend on the undeclared parameters for all inputs.

例如,請考量 SELECT dbo.tbl(@p1) + c1 FROM t1 WHERE c2 = @p2 + 2 查詢。For example, consider the query SELECT dbo.tbl(@p1) + c1 FROM t1 WHERE c2 = @p2 + 2. 運算式 dbo.tbl (@p1) + c1 和 c2 具有資料類型,而 expression @p1 和 @p2 + 2 則否。The expressions dbo.tbl(@p1) + c1 and c2 have data types, and expression @p1 and @p2 + 2 do not.

在此步驟之後,如果任何運算式 (UDF 呼叫以外) 有兩個不含資料類型的引數,類型推算會失敗並出現錯誤。After this step, if any expression (other than a call to a UDF) has two arguments without data types, type deduction fails with an error. 例如,下列範例都會產生錯誤:For example, the following all produce errors:

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

下列範例不會產生錯誤:The following example does not produce an error:

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

步驟2Step 2

若為指定的未宣告參數 @p,類型推算演算法會尋找包含 @p 且為下列其中一項的最內層運算式 E (@p):For a given undeclared parameter @p, the type deduction algorithm finds the innermost expression E(@p) that contains @p and is one of the following:

  • 比較或指派運算子的引數。An argument to a comparison or assignment operator.

  • 使用者定義函數 (包括資料表值 UDF)、程序或方法的引數。An argument to a user-defined function (including table-valued UDF), procedure, or method.

  • INSERT語句的VALUES子句的引數。An argument to a VALUES clause of an INSERT statement.

  • CASTCONVERT的引數。An argument to a CAST or CONVERT.

類型推算演算法會尋找 E (@p)的目標資料類型 TT (@p)。The type deduction algorithm finds a target data type TT(@p) for E(@p). 上述範例的目標資料類型如下所示:Target data types for the previous examples are as follows:

  • 比較或指派另一端的資料類型。The data type of the other side of the comparison or assignment.

  • 傳遞此引數之目標參數的已宣告資料類型。The declared data type of the parameter to which this argument is passed.

  • 插入此值之資料行的資料類型。The data type of the column into which this value is inserted.

  • 陳述式轉型或轉換成的資料類型。The data type to which the statement is casting or converting.

例如,請考量 SELECT * FROM t WHERE @p1 = dbo.tbl(@p2 + c1) 查詢。For example, consider the query SELECT * FROM t WHERE @p1 = dbo.tbl(@p2 + c1). 然後,E (@p1) = @p1,E (@p2) = @p2 + c1,TT (@p1)是宣告的傳回資料類型 dbo.tbl,TT (@p2)是 dbo.tbl 的宣告參數資料類型。Then E(@p1) = @p1, E(@p2) = @p2 + c1, TT(@p1) is the declared return data type of dbo.tbl, and TT(@p2) is the declared parameter data type for dbo.tbl.

如果 @p 並未包含在步驟2開頭所列的任何運算式中,則類型推算演算法會判斷 E (@p)是包含 @p 的最大純量運算式,而類型推算演算法不會計算 E (@p)的目標資料類型 TT (@p)。If @p is not contained in any expression listed at the beginning of step 2, the type deduction algorithm determines that E(@p) is the largest scalar expression that contains @p, and the type deduction algorithm does not compute a target data type TT(@p) for E(@p). 例如,如果查詢是 SELECT @p + 2 則 E (@p) = @p + 2,而且沒有 TT (@p)。For example, if the query is SELECT @p + 2 then E(@p) = @p + 2, and there is no TT(@p).

步驟3Step 3

現在已識別 E (@p)和 TT (@p),類型推算演算法會以下列兩種方式之一,會推算 @p 的資料類型:Now that E(@p) and TT(@p) are identified, the type deduction algorithm deduces a data type for @p in one of the following two ways:

  • 簡單推算Simple deduction

    如果 E (@p) = @p 和 TT (@p)存在,也就是如果 @p 直接是步驟2開頭所列其中一個運算式的引數,則類型推算演算法會將 @p 的資料類型會推算為 TT (@p)。If E(@p) = @p and TT(@p) exists, i.e., if @p is directly an argument to one of the expressions listed at the beginning of step 2, the type deduction algorithm deduces the data type of @p to be TT(@p). 例如:For example:

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

    @p1、@p2 和 @p3 的資料類型會分別是 c1 的資料類型、dbo.tbl 的傳回資料類型,以及 dbo.tbl 的參數資料類型。The data type for @p1, @p2, and @p3 will be the data type of c1, the return data type of dbo.tbl, and the parameter data type for dbo.tbl respectively.

    在特殊情況下,如果 @p 是 <、>、<= 或 > = 運算子的引數,則不適用簡單的推算規則。As a special case, if @p is an argument to a <, >, <=, or >= operator, simple deduction rules do not apply. 類型推算演算法會使用下一節所述的一般推算規則。The type deduction algorithm will use the general deduction rules explained in the next section. 例如,如果 c1 是資料類型 char(30) 的資料行,請考慮下列兩個查詢:For example, if c1 is a column of data type char(30), consider the following two queries:

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

    在第一個案例中,類型推算演算法會會推算char (30) 做為根據本主題稍早規則 @p 的資料類型。In the first case, the type deduction algorithm deduces char(30) as the data type for @p as per rules earlier in this topic. 在第二個案例中,類型推算演算法會根據下一節中的一般推算規則來會推算Varchar (8000)In the second case, the type deduction algorithm deduces varchar(8000) according to the general deduction rules in the next section.

  • 一般推算General deduction

    如果不適用簡單推算,針對未宣告的參數,下列資料類型會列入考量:If simple deduction does not apply, the following data types are considered for undeclared parameters:

    • 整數資料類型(bitTinyintSmallintintBigintInteger data types (bit, tinyint, smallint, int, bigint)

    • Money 資料類型(smallmoneymoneyMoney data types (smallmoney, money)

    • 浮點資料類型(floatrealFloating-point data types (float, real)

    • 數位(38,19) -不考慮其他數值或十進位資料類型。numeric(38, 19) - Other numeric or decimal data types are not considered.

    • Varchar (8000)Varchar (max)Nvarchar (4000)Nvarchar (max) -不考慮其他字串資料類型(例如textchar (8000)Nvarchar (30) 等等)。varchar(8000), varchar(max), nvarchar(4000), and nvarchar(max) - Other string data types (such as text, char(8000), nvarchar(30), etc.) are not considered.

    • Varbinary (8000)Varbinary (max) -不考慮其他二進位資料類型(例如imagebinary (8000)Varbinary (30) 等等)。varbinary(8000) and varbinary(max) - Other binary data types are not considered (such as image, binary(8000), varbinary(30), etc.).

    • datetime (7)Smalldatetimedatetimedatetime2 (7)datetimeoffset (7) -不考慮其他日期和時間類型,例如time (4)date, time(7), smalldatetime, datetime, datetime2(7), datetimeoffset(7) - Other date and time types, such as time(4), are not considered.

    • sql_variantsql_variant

    • xmlxml

    • CLR 系統定義的類型(hierarchyidgeometrygeographyCLR system-defined types (hierarchyid, geometry, geography)

    • CLR 使用者定義型別CLR user-defined types

選取準則Selection Criteria

在候選資料類型中,會導致查詢失效的任何資料類型都會遭到拒絕。Of the candidate data types, any data type that would invalidate the query is rejected. 在剩餘的候選資料類型中,類型推算演算法會根據下列規則來選取其中一個資料類型。Of the remaining candidate data types, the type deduction algorithm selects one according to the following rules.

  1. 選取的資料類型會在 E (@p)中,產生最小的隱含轉換數目。The data type that produces the smallest number of implicit conversions in E(@p) is selected. 如果特定資料類型針對 E (@p)產生與 TT (@p)不同的資料類型,則類型推算演算法會將此視為從 E (@p)的資料類型到 TT (@p)的額外隱含轉換。If a particular data type produces a data type for E(@p) that is different from TT(@p), the type deduction algorithm considers this to be an extra implicit conversion from the data type of E(@p) to TT(@p).

    例如:For example:

    SELECT * FROM t WHERE Col_Int = Col_Int + @p  
    

    在此情況下,E (@p)是 Col_Int + @p 和 TT (@p)是Int。已為 @p 選擇int ,因為它不會產生隱含轉換。In this case, E(@p) is Col_Int + @p and TT(@p) is int. int is chosen for @p because it produces no implicit conversions. 任何其他資料類型選擇會產生至少一個隱含轉換。Any other choice of data type produces at least one implicit conversion.

  2. 如果多個資料類型有相同的最小轉換數目,則會使用具有較高優先順序的資料類型。If multiple data types tie for the smallest number of conversions, the data type with greater precedence is used. 例如:For example

    SELECT * FROM t WHERE Col_Int = Col_smallint + @p  
    

    在此情況下, intSmallint會產生一個轉換。In this case, int and smallint produce one conversion. 所有其他的資料類型都會產生一個以上的轉換。Every other data type produces more than one conversion. 因為int的優先順序高於Smallint,所以會將int用於 @p。Because int takes precedence over smallint, int is used for @p. 如需資料類型優先順序的詳細資訊,請參閱資料(類型優先順序 transact-sql)For more information about data type precedence, see Data Type Precedence (Transact-SQL).

    只有在依據規則 1 每個候選資格相同的資料類型與具有最高優先順序的資料類型之間發生隱含轉換時,才會套用此規則。This rule only applies if there is an implicit conversion between every data type that ties according to rule 1 and the data type with the greatest precedence. 如果沒有隱含轉換,資料類型推算會失敗並出現錯誤。If there is no implicit conversion, then data type deduction fails with an error. 例如,在查詢 SELECT @p FROM t中,資料類型推算會失敗,因為 @p 的任何資料類型都同樣好用。For example in the query SELECT @p FROM t, data type deduction fails because any data type for @p would be equally good. 例如,沒有從intxml的隱含轉換。For example, there is no implicit conversion from int to xml.

  3. 如果兩個類似的資料類型系結在規則1下(例如Varchar (8000)Varchar (max) ),則會選擇較小的資料類型(Varchar (8000) )。If two similar data types tie under rule 1, for example varchar(8000) and varchar(max), the smaller data type (varchar(8000)) is chosen. 相同的原則也適用于NvarcharVarbinary資料類型。The same principle applies to nvarchar and varbinary data types.

  4. 為執行規則 1,類型推算演算法會偏好特定轉換。For purposes of rule 1, the type deduction algorithm prefers certain conversions as better than others. 從最佳到最差的轉換順序如下:Conversions in order from best to worst are:

    1. 不同長度的同一個基本資料類型之間的轉換。Conversion between same basic data type of different length.

    2. 在固定長度和可變長度版本的相同資料類型(例如charVarchar)之間進行轉換。Conversion between fixed-length and variable-length version of same data types (e.g., char to varchar).

    3. Nullint之間的轉換。Conversion between NULL and int.

    4. 任何其他轉換。Any other conversion.

例如,針對查詢 SELECT * FROM t WHERE [Col_varchar(30)] > @p,會選擇Varchar (8000) ,因為轉換(a)是最佳做法。For example, for the query SELECT * FROM t WHERE [Col_varchar(30)] > @p, varchar(8000) is chosen because conversion (a) is best. 針對查詢 SELECT * FROM t WHERE [Col_char(30)] > @p,仍然會選擇Varchar (8000) ,因為它會導致類型(b)轉換,而且因為另一個選擇(例如Varchar (4000) )會導致類型(d)轉換。For the query SELECT * FROM t WHERE [Col_char(30)] > @p, varchar(8000) is still chosen because it causes a type (b) conversion, and because another choice (such as varchar(4000)) would cause a type (d) conversion.

最後一個範例是,假設查詢 SELECT NULL + @p,就會為 @p 選擇int ,因為它會導致類型(c)轉換。As a final example, given a query SELECT NULL + @p, int is chosen for @p because it results in a type (c) conversion.

PermissionsPermissions

需要執行 @tsql 引數的許可權。Requires permission to execute the @tsql argument.

範例Examples

下列範例會傳回未宣告之 @id@name 參數的預期資料類型相關資訊。The following example returns information such as the expected data type for the undeclared @id and @name parameters.

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 參數。When the @id parameter is provided as a @params reference, the @id parameter is omitted from the result set and only the @name parameter is described.

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'  
  

另請參閱See Also

sp_describe_first_result_set (transact-sql) sp_describe_first_result_set (Transact-SQL)
dm_exec_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)sys.dm_exec_describe_first_result_set_for_object (Transact-SQL)