sp_describe_undeclared_parameters (Transact-SQL)
適用於：
SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Microsoft Fabric 中的 SQL 分析端點 Microsoft Fabric 中的倉儲
傳回結果集，其中包含 Transact-SQL 批次中未宣告參數的相關元數據。 請考慮@tsql批次中使用的每個參數，但未在 @params 中宣告。 傳回結果集，其中包含每個這類參數的一個數據列，以及該參數的推斷型別資訊。 如果@tsql輸入批次除了在 @params 中宣告的參數之外，此程式會傳回空的結果集。
Syntax
sp_describe_undeclared_parameters
[ @tsql = ] 'Transact-SQL_batch'
[ , [ @params = ] N'@parameter_name data_type [ , ... n ]' ]
注意
若要在專用 SQL 集區中的 Azure Synapse Analytics 中使用這個預存程式，請將資料庫相容性層級設定為 20 或更新版本。 若要退出離開，請將資料庫相容性層級變更為 10。
引數
[ @tsql = ] 'tsql'
一或多個 Transact-SQL 語句。 @tsql可能是 nvarchar（n） 或 nvarchar（max）。
[ @params = ] N'@parameter_name data_type [ ,... n ]'
@params提供 Transact-SQL 批次參數的宣告字串，類似於運作方式sp_executesql。 @params可能是 nvarchar（n） 或 nvarchar（max）。
字串，包含內嵌在 @tsql 中之所有參數的定義。 字串必須是 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 | 包含結果集中參數的序數位置。 第一個參數的位置會指定為 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) | 包含數據類型名稱。 包含為參數數據類型指定的自變數（例如長度、有效位數、小數字數）。 如果數據類型是使用者定義的別名類型，則會在這裡指定基礎系統類型。 如果是CLR使用者定義資料類型， NULL 則會在此數據行中傳回。 如果無法推斷參數的類型， NULL 則會傳回 。 可為 Null。 |
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。 可為 Null。 |
suggested_user_type_database |
sysname | 針對 CLR 和別名類型，包含定義型別的資料庫名稱。 否則為 NULL。 可為 Null。 |
suggested_user_type_schema |
sysname | 針對 CLR 和別名類型，包含定義型別的架構名稱。 否則為 NULL。 可為 Null。 |
suggested_user_type_name |
sysname | 針對 CLR 和別名類型，包含類型的名稱。 否則為 NULL。 |
suggested_assembly_qualified_type_name |
nvarchar(4000) | 針對 CLR 類型，傳回定義型別的元件和類別名稱。 否則為 NULL。 可為 Null。 |
suggested_xml_collection_id |
int | 包含 xml_collection_id 參數的 數據類型，如 中所 sys.columns指定。 如果傳回的類型與 XML 架構集合沒有關聯，則此數據行會 NULL 傳回。 可為 Null。 |
suggested_xml_collection_database |
sysname | 包含定義與這個類型相關聯之 XML 架構集合的資料庫。 如果傳回的類型與 XML 架構集合沒有關聯，則此數據行會 NULL 傳回。 可為 Null。 |
suggested_xml_collection_schema |
sysname | 包含定義與這個類型相關聯之 XML 架構集合的架構。 如果傳回的類型與 XML 架構集合沒有關聯，則此數據行會 NULL 傳回。 可為 Null。 |
suggested_xml_collection_name |
sysname | 包含與此類型相關聯的 XML 架構集合名稱。 如果傳回的類型與 XML 架構集合沒有關聯，則此數據行會 NULL 傳回。 可為 Null。 |
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。 可為 Null。 |
suggested_tds_type_id |
int | 供內部使用。 不可為 Null。 |
suggested_tds_length |
int | 供內部使用。 不可為 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 中宣告的參數以外，程式會傳回空的結果集。
注意
您必須將變數宣告為純量 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）、程式或方法。
- 語句子句的
INSERT自變數VALUES。 - 或
CONVERT的CAST自變數。
類型推算演算法會尋找 的目標E(@p)數據類型TT(@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 + 2 E(@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);、 和
@p3的數據類型@p1@p2將會是 的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）
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） - 其他二進位數據類型不考慮（例如 image、 binary（8000）、 varbinary（30）等。
date、time（7）、smalldatetime、datetime、datetime2（7）、datetimeoffset（7） - 其他日期和時間類型，例如 time（4），則不會考慮。
sql_variant
xml
CLR 系統定義的類型 （hierarchyid， geometry， geography）
CLR 使用者定義型別
選取準則
在候選數據類型中，任何會使查詢失效的數據類型都會遭到拒絕。 在其餘候選數據類型中，類型推算演算法會根據下列規則選取一個。
選取中產生最小隱含轉換
E(@p)數目的數據類型。 如果特定數據類型產生與 不同的TT(@p)數據類型E(@p)，則類型推算演算法會將這視為從數據類型E(@p)TT(@p)到的額外隱含轉換。例如：
SELECT * FROM t WHERE Col_Int = Col_Int + @p;在這裡情況下，
E(@p)是Col_Int + @p且TT(@p)為 int。 因為 int 不會產生隱含轉換，所以選擇@pint。 數據類型的任何其他選擇都會產生至少一個隱含轉換。如果多個數據類型係結為最少的轉換數目，則會使用優先順序較高的數據類型。 例如：
SELECT * FROM t WHERE Col_Int = Col_smallint + @p;在此情況下， int 和 smallint 會產生一個轉換。 所有其他數據類型都會產生一個以上的轉換。 因為 int 的優先順序高於 smallint， 因此 int 會用於
@p。 如需數據類型優先順序的詳細資訊，請參閱 數據類型優先順序。只有在每個根據規則 1 系結的數據類型與優先順序最高的數據類型之間有隱含轉換時，才會套用此規則。 如果沒有隱含轉換，數據類型推算就會失敗，並出現錯誤。 例如，在查詢
SELECT @p FROM t中，數據類型推算會失敗，因為的任何數據類型@p都同樣良好。 例如，沒有從 int 到 xml 的隱含轉換。如果規則 1 下有兩個類似的數據類型，例如 varchar（8000） 和 varchar（max），則會選擇較小的數據類型 （varchar（8000）。 相同的原則適用於 nvarchar 和 varbinary 數據類型。
基於規則 1 的目的，類型推算演算法偏好某些轉換比其他轉換更好。 從最佳轉換到最差的轉換如下：
- 不同長度的相同基本數據類型之間的轉換。
- 相同數據類型的固定長度和可變長度版本之間的轉換（例如 char 到 varchar）。
- 與 int 之間的
NULL轉換。 - 任何其他轉換。
例如，針對查詢 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 參數的預期數據類型。
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';