sp_describe_undeclared_parameters (Transact-SQL)

  • 發行項

傳回一個結果集，其中包含有關在 Transact-SQL 批次中未宣告之參數的中繼資料。請考慮在 @tsql 批次中使用，但未在 @params 中宣告的每個參數。針對每個此類參數，會傳回其中包含一個資料列的結果集，以及針對該參數推斷出的類型資訊。 如果除了 @params 中已宣告的參數之外，@tsql 輸入批次沒有參數，此程序會傳回空白結果集。

主題連結圖示 Transact-SQL 語法慣例

語法

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。

  • 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

包含 sys.types 中所指定參數之資料類型的 system_type_id。

針對 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， 以查看 precision 資料行的說明。

suggested_scale

tinyint NOT NULL

請參閱 sys.columns， 以查看 scale 資料行的說明。

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 批次是否有效時，批次在查詢最佳化期間或執行期間導致的任何錯誤都不會列入考量。

  • 如果 @ 參數不是 NULL，且包含的字串不是句法有效的參數宣告字串，或是如果它包含的字串會多次宣告任何參數。

  • 如果輸入 Transact-SQL 批次宣告的區域變數名稱與 @ 參數中所宣告的參數名稱相同。

  • 如果陳述式建立暫存資料表。

如果除了 @params 中已宣告的參數之外，@tsql 沒有參數，此程序會傳回空白結果集。

參數選取演算法

針對具有未宣告之參數的查詢，在三個步驟中進行未宣告之參數的資料類型推算。

步驟 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)

    • 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 使用者定義型別

選取準則

在候選資料類型中，會導致查詢失效的任何資料類型都會遭到拒絕。 在剩餘的候選資料類型中，類型推算演算法會根據下列規則來選取其中一個資料類型。

  1. 選取會在 E(@p) 中產生最小隱含轉換數目的資料類型。 如果特定資料類型為 E(@p) 產生不同於 TT(@p) 的資料類型，類型推算演算法會認為這是從 E(@p) 的資料類型到 TT(@p) 的額外隱含轉換。

    例如：

    SELECT * FROM t WHERE Col_Int = Col_Int + @p

    在此案例中，E(@p) is Col_Int + @p and TT(@p) 是 int。 因為 int 不會產生隱含轉換，因此會為 @p 選擇此值。 任何其他資料類型選擇會產生至少一個隱含轉換。

  2. 如果多個資料類型有相同的最小轉換數目，則會使用具有較高優先順序的資料類型。 例如：

    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 沒有隱含轉換。

  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)