sp_describe_undeclared_parameters (Transact-SQL)

發行項
12/22/2023

適用於：Microsoft Fabric 中 Microsoft Fabric 倉儲中的 SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse AnalyticsSQL 分析端點

傳回結果集，其中包含 Transact-SQL 批次中未宣告參數的相關元數據。請考慮@tsql批次中使用的每個參數，但未在 @params 中宣告。傳回結果集，其中包含每個這類參數的一個數據列，以及該參數的推斷型別資訊。如果@tsql輸入批次除了在 @params 中宣告的參數之外，此程式會傳回空的結果集。

Transact-SQL 語法慣例

Syntax

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

注意

若要在專用 SQL 集區中的 Azure Synapse Analytics 中使用這個預存程式，請將資料庫相容性層級設定為 20 或更新版本。若要退出宣告，請將資料庫相容性層級變更為 10。

引數

[ @tsql = ] 'Transact-SQL\_batch' 一或多個 Transact-SQL 語句。 Transact-SQL_batch可能是 nvarchar（n）或 nvarchar（max）。

[ @params = ]N'parameters'' @params提供 Transact-SQL 批次參數的宣告字串，類似於sp_executesql運作方式。參數可以是 nvarchar（n）或 nvarchar（max）。

這是一個字串，其中包含所有已內嵌在 Transact-SQL_batch 的參數定義。字串必須是 Unicode 常數或 Unicode 變數。每個參數定義都由參數名稱和資料類型組成。 n 是一個預留位置，表示其他參數定義。如果語句中的 Transact-SQL 語句或批次不包含參數，則不需要@params。這個參數的預設值是 NULL。

Datatype
參數的資料類型。

傳回碼值

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	包含參數數據類型的system_type_id ，如 sys.types 中所指定。針對 CLR 類型，即使 system_type_name 數據行會傳回 NULL，這個數據行也會傳回值 240。
suggested_system_type_name	nvarchar （256） NULL	包含數據類型名稱。包含為參數數據類型指定的自變數（例如長度、有效位數、小數字數）。如果數據類型是使用者定義的別名類型，則會在這裡指定基礎系統類型。如果是 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 和別名類型，包含數據行數據類型的user_type_id，如 sys.types 中所指定。否則為 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	包含參數數據類型的xml_collection_id，如 sys.columns 中所指定。如果傳回的類型與 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 中宣告的參數以外，程式會傳回空的結果集。

注意

您必須將變數宣告為純量 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 子句的自變數。
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 的宣告參數數據類型。

如果@p不包含在步驟 2 開頭所列的任何運算式中，類型推算演算法會判斷 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）
- 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 不會產生隱含轉換，所以選擇 int 進行@p。數據類型的任何其他選擇都會產生至少一個隱含轉換。
如果多個數據類型係結為最少的轉換數目，則會使用優先順序較高的數據類型。例如：
```
SELECT * FROM t WHERE Col_Int = Col_smallint + @p  
```
在此情況下， int 和 smallint 會產生一個轉換。所有其他數據類型都會產生一個以上的轉換。因為 int 的優先順序高於 smallint， 因此 int 會用於@p。如需資料類型優先順序的詳細資訊，請參閱資料類型優先順序（Transact-SQL）。

只有在根據規則 1 系結的每個資料類型與優先順序最高的數據類型之間有隱含轉換時，才會套用此規則。如果沒有隱含轉換，數據類型推算會失敗並出現錯誤。例如，在查詢 SELECT @p FROM t中，數據類型推算會失敗，因為@p的任何數據類型都同樣良好。例如，沒有從 int 到 xml 的隱含轉換。
如果規則 1 下有兩個類似的數據類型，例如 varchar（8000） 和 varchar（max），則會選擇較小的數據類型（varchar（8000）。相同的原則適用於 nvarchar 和 varbinary 數據類型。
基於規則 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 參數的預期數據類型。

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_undeclared_parameters (Transact-SQL)

Syntax

引數

傳回碼值

結果集

備註

參數選取演算法

選取準則

權限

範例

另請參閱

意見反應

意見反應

其他資源