sp_describe_first_result_set (Transact-SQL)sp_describe_first_result_set (Transact-SQL)

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

第一個可能結果集傳回的中繼資料Transact-SQLTransact-SQL批次。Returns the metadata for the first possible result set of the Transact-SQLTransact-SQL batch. 如果批次沒有傳回任何結果,就會傳回空的結果集。Returns an empty result set if the batch returns no results. 如果引發錯誤Database EngineDatabase Engine無法判別將透過執行靜態分析來執行的第一個查詢的中繼資料。Raises an error if the Database EngineDatabase Engine cannot determine the metadata for the first query that will be executed by performing a static analysis. 動態管理檢視sys.dm_exec_describe_first_result_set (TRANSACT-SQL) 傳回的相同資訊。The dynamic management view sys.dm_exec_describe_first_result_set (Transact-SQL) returns the same information.

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

語法Syntax

  
sp_describe_first_result_set [ @tsql = ] N'Transact-SQL_batch'   
    [ , [ @params = ] N'parameters' ]   
    [ , [ @browse_information_mode = ] <tinyint> ] ]  

引數Arguments

[ \@tsql = ] 'Transact-SQL_batch' 一或多個Transact-SQLTransact-SQL陳述式。[ \@tsql = ] 'Transact-SQL_batch' One or more Transact-SQLTransact-SQL statements. Transact 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, which is similar to sp_executesql. 參數可能nvarchar (n) 或是nvarchar (max)Parameters may be nvarchar(n) or nvarchar(max).

是一個字串,其中包含已內嵌在的所有參數的定義Transact-SQLTransact-SQL _batch&ltIs one string that contains the definitions of all parameters that have been embedded in the Transact-SQLTransact-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. 陳述式中指定的每個參數必須定義在@params。Every parameter specified in the statement must be defined in @params. 如果Transact-SQLTransact-SQL陳述式或陳述式中的批次不包含參數, @params 並非必要。If the Transact-SQLTransact-SQL statement or batch in the statement does not contain parameters, @params is not required. 這個參數的預設值是 NULL。NULL is the default value for this parameter.

[ \@browse_information_mode = ] tinyint 指定是否傳回其他索引鍵資料行和來源資料表資訊。[ \@browse_information_mode = ] tinyint Specifies if additional key columns and source table information are returned. 如果設定為 1,就會分析每個查詢,如同查詢上包含 FOR BROWSE 選項一樣。If set to 1, each query is analyzed as if it includes a FOR BROWSE option on the query. 會傳回其他索引鍵資料行和來源資料表資訊。Additional key columns and source table information are returned.

  • 如果設為 0,則不會傳回資訊。If set to 0, no information is returned.

  • 如果設定為 1,就會分析每個查詢,如同查詢上包含 FOR BROWSE 選項一樣。If set to 1, each query is analyzed as if it includes a FOR BROWSE option on the query. 這會傳回基底資料表名稱做為來源資料行資訊。This will return base table names as the source column information.

  • 如果設定為 2,就會分析每個查詢,如同查詢用於準備或執行資料指標一樣。If set to 2, each query is analyzed as if it would be used in preparing or executing a cursor. 這會傳回檢視表名稱做為來源資料行資訊。This will return view names as source column information.

傳回碼值Return Code Values

sp_describe_first_result_set一律會傳回狀態零成功。sp_describe_first_result_set always returns a status of zero on success. 如果此程序擲回錯誤,而且程序被當做 RPC 來呼叫,傳回的狀態會填入 sys.dm_exec_describe_first_result_set 之 error_type 資料行中所述的錯誤類型。If the procedure throws an error and the procedure is called as an RPC, return status is populated by the type of error 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 when there is an error.

結果集Result Sets

這個通用的中繼資料會當做結果集來傳回,而結果中繼資料中的每個資料行都會有一個資料列。This common metadata is returned as a result set with one row for each column in the results metadata. 每個資料列都會使用下一節所描述的格式來描述資料行的類型和 Null 屬性。Each row describes the type and nullability of the column in the format described in the following section. 如果每個控制項路徑都沒有第一個陳述式,就會傳回具有零個資料列的結果集。If the first statement does not exist for every control path, a result set with zero rows is returned.

資料行名稱Column name 資料類型Data type 描述Description
is_hiddenis_hidden 位元 NOT NULLbit NOT NULL 指出資料行是為了用來瀏覽資訊而加入的額外資料行,且不會實際顯示在結果集中。Indicates that the column is an extra column added for browsing information purposes and that it does not actually appear in the result set.
column_ordinalcolumn_ordinal int NOT NULLint NOT NULL 包含資料行在結果集中的序數位置。Contains the ordinal position of the column in the result set. 第一個資料行的位置會指定為 1。The first column's position will be specified as 1.
namename sysname 為 NULLsysname NULL 如果可以判別名稱,則包含資料行名稱。Contains the name of the column if a name can be determined. 否則,它將會包含 NULL。Otherwise, it will contain NULL.
is_nullableis_nullable 位元 NOT NULLbit NOT NULL 如果資料行允許 NULL 則包含值 1,如果資料行不允許 NULL 則包含 0,此外,如果無法判別資料行是否允許 NULL,則為 1。Contains the value 1 if the column allows NULLs, 0 if the column does not allow NULLs, and 1 if it cannot be determined if the column allows NULLs.
system_type_idsystem_type_id int NOT NULLint NOT NULL 包含 sys.types 中所指定的資料行的資料類型的 system_type_id。Contains the system_type_id of the data type of the column 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.
system_type_namesystem_type_name nvarchar(256) NULLnvarchar(256) NULL 包含名稱和引數 (例如長度、有效位數、小數位數),已指定給資料行的資料類型。Contains the name and arguments (such as length, precision, scale), specified for the data type of the column. 如果資料類型是使用者定義的別名類型,這裡就會指定基礎系統類型。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 type, NULL is returned in this column.
max_lengthmax_length smallint 非 NULLsmallint NOT NULL 資料行的最大長度 (以位元組為單位)。Maximum length (in bytes) of the column.

-1 = 資料行資料類型是varchar (max)nvarchar (max)varbinary (max) ,或xml-1 = Column data type is varchar(max), nvarchar(max), varbinary(max), or xml.

針對文字資料行max_length值會是 16,或是所設定的值sp_tableoption 'text in row'For text columns, the max_length value will be 16 or the value set by sp_tableoption 'text in row'.
有效位數precision tinyint NOT NULLtinyint NOT NULL 如果以數值為基礎,就是資料行的有效位數。Precision of the column if numeric-based. 否則傳回 0。Otherwise returns 0.
scalescale tinyint NOT NULLtinyint NOT NULL 如果是以數值為基礎,便是資料行的小數位數。Scale of column if numeric-based. 否則傳回 0。Otherwise returns 0.
collation_namecollation_name sysname 為 NULLsysname NULL 如果是以字元為基礎,便是資料行的定序名稱。Name of the collation of the column if character-based. 否則,便傳回 NULL。Otherwise returns NULL.
user_type_iduser_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.
user_type_databaseuser_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.
user_type_schemauser_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.
user_type_nameuser_type_name sysname 為 NULLsysname NULL 針對 CLR 和別名類型,會包含類型的名稱。For CLR and alias types, contains the name of the type. 否則,便為 NULL。Otherwise is NULL.
assembly_qualified_type_nameassembly_qualified_type_name nvarchar(4000)nvarchar(4000) 針對 CLR 類型,會傳回定義類型之組件與類別的名稱。For CLR types, returns the name of the assembly and class defining the type. 否則,便為 NULL。Otherwise is NULL.
xml_collection_idxml_collection_id int NULLint NULL 包含 sys.columns 中所指定資料行之資料類型的 xml_collection_id。Contains the xml_collection_id of the data type of the column as specified in sys.columns. 如果傳回的類型沒有與 XML 結構描述集合相關聯,這個資料行將傳回 NULL。This column will return NULL if the type returned is not associated with an XML schema collection.
xml_collection_databasexml_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.
xml_collection_schemaxml_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.
xml_collection_namexml_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.
is_xml_documentis_xml_document 位元 NOT NULLbit NOT NULL 如果正要傳回的資料類型是 XML,而且該類型保證是完整 XML 文件 (包含根節點),而不是 XML 片段,則傳回 1,Returns 1 if the returned data type is XML and that type is guaranteed to be a complete XML document (including a root node), as opposed to an XML fragment). 否則傳回 0。Otherwise returns 0.
is_case_sensitiveis_case_sensitive 位元 NOT NULLbit NOT NULL 如果資料行是區分大小寫的字串類型,則傳回 1;否則,便傳回 0。Returns 1 if the column is a case-sensitive string type and 0 if it is not.
is_fixed_length_clr_typeis_fixed_length_clr_type 位元 NOT NULLbit NOT NULL 如果資料行是固定長度的 CLR 類型,則傳回 1;否則,便傳回 0。Returns 1 if the column is a fixed-length CLR type and 0 if it is not.
source_serversource_server sysnamesysname 在這個結果中的資料行所傳回的原始伺服器名稱 (如果它來自遠端伺服器)。Name of the originating server returned by the column in this result (if it originates from a remote server). 給定的名稱會出現在 sys.servers 中。The name is given as it appears in sys.servers. 如果資料行來自本機伺服器,或是如果無法判別其原始伺服器,則傳回 NULL。Returns NULL if the column originates on the local server or if it cannot be determined which server it originates on. 只會在要求瀏覽資訊時填入。Is only populated if browsing information is requested.
source_databasesource_database sysnamesysname 這個結果中的資料行所傳回之原始資料庫名稱。Name of the originating database returned by the column in this result. 如果無法判別資料庫,則傳回 NULL。Returns NULL if the database cannot be determined. 只會在要求瀏覽資訊時填入。Is only populated if browsing information is requested.
source_schemasource_schema sysnamesysname 這個結果中的資料行所傳回之原始結構描述名稱。Name of the originating schema returned by the column in this result. 如果無法判別結構描述,則傳回 NULL。Returns NULL if the schema cannot be determined. 只會在要求瀏覽資訊時填入。Is only populated if browsing information is requested.
source_tablesource_table sysnamesysname 這個結果的資料行所傳回之原始資料表名稱。Name of the originating table returned by the column in this result. 如果無法判別資料表,則傳回 NULL。Returns NULL if the table cannot be determined. 只會在要求瀏覽資訊時填入。Is only populated if browsing information is requested.
source_columnsource_column sysnamesysname 結果資料行所傳回之原始資料行名稱。Name of the originating column returned by the result column. 如果無法判別資料行,則傳回 NULL。Returns NULL if the column cannot be determined. 只會在要求瀏覽資訊時填入。Is only populated if browsing information is requested.
is_identity_columnis_identity_column 位元 NULLbit NULL 如果資料行是識別欄位,則傳回 1;如果不是,則傳回 0。Returns 1 if the column is an identity column and 0 if not. 如果它無法判別資料行是否為識別欄位,則傳回 NULL。Returns NULL if it cannot be determined that the column is an identity column.
is_part_of_unique_keyis_part_of_unique_key 位元 NULLbit NULL 如果資料行是唯一索引 (包括唯一和主要的條件約束) 的一部分,則傳回 1;如果不是,則傳回 0。Returns 1 if the column is part of a unique index (including unique and primary constraint) and 0 if not. 如果它無法判別資料行是否為唯一索引的一部分,則傳回 NULL。Returns NULL if it cannot be determined that the column is part of a unique index. 只會在要求瀏覽資訊時填入。Only populated if browsing information is requested.
is_updateableis_updateable 位元 NULLbit NULL 如果資料行是可更新的,則傳回 1;如果不是,則傳回 0。Returns 1 if the column is updateable and 0 if not. 如果它無法判別資料行是否可更新,則傳回 NULL。Returns NULL if it cannot be determined that the column is updateable.
is_computed_columnis_computed_column 位元 NULLbit NULL 如果資料行是計算資料行,則傳回 1;如果不是,則傳回 0。Returns 1 if the column is a computed column and 0 if not. 如果它無法判別資料行是否為計算資料行,則傳回 NULL。Returns NULL if it cannot be determined that the column is a computed column.
is_sparse_column_setis_sparse_column_set 位元 NULLbit NULL 如果資料行是疏鬆資料行,則傳回 1,否則傳回 0。Returns 1 if the column is a sparse column and 0 if not. 如果它無法判別資料行是否為疏鬆資料行集的一部分,則傳回 NULL。Returns NULL if it cannot be determined that the column is part of a sparse column set.
ordinal_in_order_by_listordinal_in_order_by_list smallint NULLsmallint NULL 這個資料行在 ORDER BY 清單中的位置。Position of this column in ORDER BY list. 如果資料行不會顯示在 ORDER BY 清單中,或如果無法唯一判別 ORDER BY 清單,則傳回 NULL。Returns NULL if the column does not appear in the ORDER BY list or if the ORDER BY list cannot be uniquely determined.
order_by_list_lengthorder_by_list_length smallint NULLsmallint NULL ORDER BY 清單的長度。Length of the ORDER BY list. 如果沒有 ORDER BY 清單,或如果無法唯一判別 ORDER BY 清單,則傳回 NULL。Returns NULL if there is no ORDER BY list or if the ORDER BY list cannot be uniquely determined. 請注意,這個值會是所傳回的所有資料列相同sp_describe_first_result_set。Note that this value will be the same for all rows returned by sp_describe_first_result_set.
order_by_is_descendingorder_by_is_descending smallint NULLsmallint NULL 如果 ordinal_in_order_by_list 不是 NULL, order_by_is_descending資料行則報告此資料行的 ORDER BY 子句方向。If the ordinal_in_order_by_list is not NULL, the order_by_is_descending column reports the direction of the ORDER BY clause for this column. 否則,它會回報 NULL。Otherwise it reports NULL.
tds_type_idtds_type_id int NOT NULLint NOT NULL 供內部使用。For internal use.
tds_lengthtds_length int NOT NULLint NOT NULL 供內部使用。For internal use.
tds_collation_idtds_collation_id int NULLint NULL 供內部使用。For internal use.
tds_collation_sort_idtds_collation_sort_id tinyint NULLtinyint NULL 供內部使用。For internal use.

備註Remarks

sp_describe_first_result_set ,如果此程序傳回的第一個結果集 (假設) 中繼資料批次 A 並且如果該批次 (A) 之後執行然後批次的保證會 (1) 引發最佳化時間錯誤,(2)會引發執行階段錯誤,(3) 會傳回任何結果集,或 (4) 傳回第一個結果集,使用相同的中繼資料所描述sp_describe_first_result_setsp_describe_first_result_set guarantees that if the procedure returns the first result-set metadata for (a hypothetical) batch A and if that batch (A) is subsequently executed then the batch will either (1) raises an optimization-time error, (2) raises a run-time error, (3) returns no result set, or (4) returns a first result set with the same metadata described by sp_describe_first_result_set.

名稱、Null 屬性和資料類型可以不同。The name, nullability, and data type can differ. 如果sp_describe_first_result_set傳回空的結果集,就可以保證批次執行會傳回任何結果集。If sp_describe_first_result_set returns an empty result set, the guarantee is that the batch execution will return no-result sets.

這項保證會假設伺服器上沒有相關的結構描述變更。This guarantee presumes there are no relevant schema changes on the server. 在伺服器上的相關結構描述變更不包括建立暫存資料表或資料表之間的時間於批次 A 中的變數, sp_describe_first_result_set稱為和期間傳回的結果集的時間執行,包括由批次 b 進行的結構描述變更Relevant schema changes on the server do not include creating a temporary tables or table variables in the batch A between the time that sp_describe_first_result_set is called and the time that the result set is returned during execution, including schema changes made by batch B.

sp_describe_first_result_set任何下列的情況下會傳回錯誤。sp_describe_first_result_set 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.

  • 如果@參數不是 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 uses a temporary table.

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

如果其他所有檢查已成功,就會將輸入批次內所有可能的控制流程路徑列入考量。If all other checks succeed, all possible control flow paths inside the input batch are considered. 此採用考慮到所有的控制流程陳述式 (GOTO、 IF/ELSE、 WHILE 及Transact-SQLTransact-SQLTRY/CATCH 區塊) 以及任何程序,動態Transact-SQLTransact-SQL批次或從輸入批次叫用 EXEC 陳述式時,DDL 陳述式而引發的觸發程序要引發的 DDL 觸發程序或 DML 陳述式會造成要引發的觸發程序,因為上的外部索引鍵條件約束的串聯式動作而遭到修改的資料表或目標資料表上。This takes into account all control flow statements (GOTO, IF/ELSE, WHILE, and Transact-SQLTransact-SQL TRY/CATCH blocks) as well as any procedures, dynamic Transact-SQLTransact-SQL batches, or triggers invoked from the input batch by an EXEC statement, a DDL statement that causes DDL triggers to be fired, or a DML statement that causes triggers to be fired on a target table or on a table that is modified because of cascading action on a foreign key constraint. 在具有許多可能的控制路徑之情況下,有時候,演算將會停止。In the case of many possible control paths, at some point an algorithm stops.

針對每個控制流程路徑中,第一個陳述式 (如果有的話),傳回結果集由sp_describe_first_result_setFor each control flow path, the first statement (if any) that returns a result set is determined by sp_describe_first_result_set.

在單一批次中找到多個可能的第一個陳述式時,其結果可能會在資料行數目、資料行名稱、Null 屬性和資料類型等方面有所不同。When multiple possible first statements are found in a batch, their results can differ in number of columns, column name, nullability, and data type. 這裡將會詳細說明這些差異的處理方式:How these differences are handled is described in more detail here:

  • 如果資料行數目不同,就會擲回錯誤,且不會傳回任何結果。If the number of columns differs, an error is thrown and no result is returned.

  • 如果資料行名稱不同,傳回的資料行名稱就會設定為 NULL。If the column name differs, the column name returned is set to NULL.

  • 如果 Null 屬性不同,傳回的 Null 屬性將會允許 NULL。It the nullability differs, the nullability returned will allow NULLs.

  • 如果資料類型不同,將會擲回錯誤,且不會傳回任何結果,除非遇到下列情況:If the data type differs, an error will be thrown and no result is returned except for the following cases:

    • varchar(a)varchar(a') 其中 ' >。varchar(a) to varchar(a') where a' > a.

    • varchar(a)varchar (max)varchar(a) to varchar(max)

    • nvarchar(a)nvarchar(a') 其中 ' >。nvarchar(a) to nvarchar(a') where a' > a.

    • nvarchar(a)nvarchar (max)nvarchar(a) to nvarchar(max)

    • varbinary(a)varbinary(a') 其中 ' >。varbinary(a) to varbinary(a') where a' > a.

    • varbinary(a)varbinary (max)varbinary(a) to varbinary(max)

sp_describe_first_result_set不支援間接遞迴。sp_describe_first_result_set does not support indirect recursion.

PermissionsPermissions

需要權限來執行@tsql 引數。Requires permission to execute the @tsql argument.

範例Examples

一般範例Typical Examples

A.A. 簡單範例Simple Example

下列範例描述從單一查詢傳回的結果集。The following example describes the result set returned from a single query.

sp_describe_first_result_set @tsql = N'SELECT object_id, name, type_desc FROM sys.indexes'  

下列範例會顯示從包含參數之單一查詢傳回的結果集。The following example shows the result set returned from a single query that contains a parameter.

sp_describe_first_result_set @tsql =   
N'SELECT object_id, name, type_desc   
FROM sys.indexes   
WHERE object_id = @id1'  
, @params = N'@id1 int'  

B.B. 瀏覽模式範例Browse Mode Examples

下列三個範例說明不同瀏覽資訊模式之間的主要差異。The following three examples illustrate the key difference between the different browse information modes. 只有相關的資料行包含在查詢結果中。Only the relevant columns have been included in the query results.

下列範例使用 0,表示未傳回任何資訊。Example using 0 indicating no information is returned.

CREATE TABLE dbo.t (a int PRIMARY KEY, b1 int);  
GO  
CREATE VIEW dbo.v AS SELECT b1 AS b2 FROM dbo.t;  
GO  
EXEC sp_describe_first_result_set N'SELECT b2 AS b3 FROM dbo.v', null, 0;  

以下為結果集:Here is the result set.

is_hiddenis_hidden column_ordinalcolumn_ordinal namename source_schemasource_schema source_tablesource_table source_columnsource_column is_part_of_unique_keyis_part_of_unique_key
00 11 b3b3 NULLNULL NULLNULL NULLNULL NULLNULL

下列範例使用 1,表示它傳回資訊,就如同在查詢中包含 FOR BROWSE 選項一樣。Example using 1 indicating it returns information as if it includes a FOR BROWSE option on the query.

EXEC sp_describe_first_result_set N'SELECT b2 AS b3 FROM v', null, 1  
  

以下為結果集:Here is the result set.

is_hiddenis_hidden column_ordinalcolumn_ordinal namename source_schemasource_schema source_tablesource_table source_columnsource_column is_part_of_unique_keyis_part_of_unique_key
00 11 b3b3 dbodbo tt B1B1 00
11 22 aa dbodbo tt aa 11

下列範例使用 2,表示已分析,就如同準備資料指標一樣。Example using 2 indicating analyzed as if you are preparing a cursor.

EXEC sp_describe_first_result_set N'SELECT b2 AS b3 FROM v', null, 2  

以下為結果集:Here is the result set.

is_hiddenis_hidden column_ordinalcolumn_ordinal namename source_schemasource_schema source_tablesource_table source_columnsource_column is_part_of_unique_keyis_part_of_unique_key
00 11 B3B3 dbodbo vv B2B2 00
11 22 ROWSTATROWSTAT NULLNULL NULLNULL NULLNULL 00

問題範例Examples of problems

下列範例會針對所有範例使用兩個資料表。The following examples use two tables for all examples. 請執行下列陳述式以建立範例資料表。Execute the following statements to create the example tables.

CREATE TABLE dbo.t1 (a int NULL, b varchar(10) NULL, c nvarchar(10) NULL);  
CREATE TABLE dbo.t2 (a smallint NOT NULL, d varchar(20) NOT NULL, e int NOT NULL);  

因為資料行數目不同而發生錯誤Error because the number of columns differ

在這個範例中,第一個可能的結果集中之資料行數目不同。Number of columns in possible first result sets differ in this example.

sp_describe_first_result_set @tsql =   
N'  
IF(1=1)  
    SELECT a FROM t1;  
ELSE  
    SELECT a, b FROM t1;  
SELECT * FROM t; -- Ignored, not a possible first result set.'  
  

因為資料類型不同而發生錯誤Error because the data types differ

不同的第一個可能的結果集中之資料行類型不同。Columns types differ in different possible first result sets.

sp_describe_first_result_set @tsql =   
N'  
IF(1=1)  
    SELECT a FROM t1;  
ELSE  
    SELECT a FROM t2;  

結果:錯誤,類型不相符 (intsmallint)。Result: Error, mismatching types (int vs. smallint).

無法判別資料行名稱Column name cannot be determined

在第一個可能的結果集中,在相同的變數長度類型、Null 屬性與資料行名稱等情況下,資料行的長度不同:Columns in possible first result sets differ by length for same variable length type, nullability, and column names:

sp_describe_first_result_set @tsql =   
N'  
IF(1=1)  
    SELECT b FROM t1;  
ELSE  
    SELECT d FROM t2; '  

結果:<未知的資料行名稱 > varchar (20) NULLResult: <Unknown Column Name> varchar(20) NULL

透過別名強制資料行名稱必須相同Column name forced to be identical through aliasing

與先前相同,但是資料行透過資料行別名而具有相同名稱。Same as previous, but columns have the same name through column aliasing.

sp_describe_first_result_set @tsql =   
N'  
IF(1=1)  
    SELECT b FROM t1;  
ELSE  
    SELECT d AS b FROM t2;'  

結果: b varchar (20) NULLResult: b varchar(20)NULL

因為無法比對資料行類型而發生錯誤Error because column types cannot be matched

在不同的第一個可能的結果集中,資料行類型彼此不同。The columns types differ in different possible first result sets.

sp_describe_first_result_set @tsql =   
N'  
IF(1=1)  
    SELECT b FROM t1;  
ELSE  
    SELECT c FROM t1;'  

結果:錯誤,類型不相符 (varchar(10)nvarchar(10)。Result: Error, mismatching types (varchar(10) vs. nvarchar(10)).

結果集可以傳回錯誤Result set can return an error

第一個結果集為錯誤或結果集。First result set is either error or result set.

sp_describe_first_result_set @tsql =   
N'  
IF(1=1)  
    RAISERROR(''Some Error'', 16, 1);  
  
ELSE  
    SELECT a FROM t1;  
SELECT e FROM t2; -- Ignored, not a possible first result set.;'  

結果: intNULLResult: a intNULL

部分程式碼路徑沒有傳回任何結果Some code paths return no results

第一個結果集為 Null 或結果集。First result set is either null or a result set.

sp_describe_first_result_set @tsql =   
N'  
IF(1=1)  
    RETURN;  
SELECT a FROM t1;'  

結果: intNULLResult: a intNULL

來自動態 SQL 的結果Result from dynamic SQL

第一個結果集為可探索的動態 SQL,因為本身屬於文字字串。First result set is dynamic SQL that is discoverable because it is a literal string.

sp_describe_first_result_set @tsql =   
N'EXEC(N''SELECT a FROM t1'');'  

結果: INT NULLResult: a INT NULL

來自動態 SQL 的失敗結果Result failure from dynamic SQL

第一個結果集因為動態 SQL 而未定義。First result set is undefined because of dynamic SQL.

sp_describe_first_result_set @tsql =   
N'  
DECLARE @SQL NVARCHAR(max);  
SET @SQL = N''SELECT a FROM t1 WHERE 1 = 1 '';  
IF(1=1)  
    SET @SQL += N'' AND e > 10 '';  
EXEC(@SQL); '  

結果:錯誤。Result: Error. 因為可探索的動態 SQL,導致結果不是可探索的。Result is not discoverable because of the dynamic SQL.

由使用者指定的結果集Result set specified by user

第一個結果集是由使用者手動指定的。First result set is specified manually by user.

sp_describe_first_result_set @tsql =   
N'  
DECLARE @SQL NVARCHAR(max);  
SET @SQL = N''SELECT a FROM t1 WHERE 1 = 1 '';  
IF(1=1)  
    SET @SQL += N'' AND e > 10 '';  
EXEC(@SQL)  
    WITH RESULT SETS(  
        (Column1 BIGINT NOT NULL)  
    ); '  

結果:Column1 bigint NOT NULLResult: Column1 bigint NOT NULL

因位結果集模稜兩可而發生錯誤Error caused by a ambiguous result set

此範例假設名為 user1 的另一位使用者具有資料行的預設結構描述 s1 中名為 t1 的資料表 ( int NOT NULL)。This example assumes that another user named user1 has a table named t1 in the default schema s1 with columns (a int NOT NULL).

sp_describe_first_result_set @tsql =   
N'  
    IF(@p > 0)  
    EXECUTE AS USER = ''user1'';  
    SELECT * FROM t1;'  
, @params = N'@p int'  

結果:錯誤。Result: Error. dbo.t1 或 s1.t1,各自具有不同數目的資料行,可以是 t1。t1 can be either dbo.t1 or s1.t1, each with a different number of columns.

即使結果集模稜兩可的結果Result even with ambiguous result set

使用與先前範例相同的假設。Use the same assumptions as the previous example.

sp_describe_first_result_set @tsql =   
N'  
    IF(@p > 0)  
    EXECUTE AS USER = ''user1'';  
    SELECT a FROM t1;'  

結果: int NULL因為 dbo.t1.a 和 s1.t1.a 都有型別int和不同的 null 屬性。Result: a int NULL because both dbo.t1.a and s1.t1.a have type int and different nullability.

另請參閱See Also

sp_describe_undeclared_parameters (Transact-SQL) sp_describe_undeclared_parameters (Transact-SQL)
sys.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)