If there are different possibilities for the first result set due to some conditional logic, but all have the same metadata, the procedure will report the metadata that they all share successfully. Here’s an example where the conditional logic is only concerned with different sorting options:

EXEC sys.sp_describe_first_result_set
  @tsql = N'IF @direction = ''ASC''
              SELECT SalesOrderID, OrderDate, TotalDue, CurrencyRateID
              FROM Sales.SalesOrderHeader
              ORDER BY SalesOrderID
            ELSE IF @direction = ''DESC''
              SELECT SalesOrderID, OrderDate, TotalDue, CurrencyRateID
              FROM Sales.SalesOrderHeader
              ORDER BY SalesOrderID DESC
            ELSE
              SELECT SalesOrderID, OrderDate, TotalDue, CurrencyRateID
              FROM Sales.SalesOrderHeader
              ORDER BY SalesOrderID;',
  @params = N'@direction AS VARCHAR(4)';

is_hidden column_ordinal name           is_nullable system_type_id 
--------- -------------- -------------- ----------- -------------- 
0         1              SalesOrderID   0           56             
0         2              OrderDate      0           61             
0         3              TotalDue       0           60             
0         4              CurrencyRateID 1           56             

system_type_name max_length precision scale collation_name
---------------- ---------- --------- ----- --------------
int              4          10        0     NULL          
datetime         8          23        3     NULL          
money            8          19        4     NULL          
int              4          10        0     NULL          

However, if the metadata of the different possibilities for the first result set are different, depending on how different, the procedure will either raise an error or return output after resolving mismatches. For example, in the following batch there are two possibilities for the first result set, and the two are incompatible in the number of result columns as well as in other ways, hence the procedure generates an error:

EXEC sys.sp_describe_first_result_set
  @tsql = N'IF @request = 1
              SELECT SalesOrderID, OrderDate, TotalDue, CurrencyRateID
              FROM Sales.SalesOrderHeader
            ELSE IF @request = 2
              SELECT SalesOrderID, SalesOrderDetailID, OrderQty, ProductID, UnitPrice
              FROM Sales.SalesOrderDetail;',
  @params = N'@request AS INT';

Msg 11509, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because the statement 'SELECT SalesOrderID, OrderDate, TotalDue, CurrencyRateID
              FROM Sales.SalesOrderHeader' is not compatible with the statement 'SELECT SalesOrderID, SalesOrderDetailID, OrderQty, ProductID, UnitPrice
              FROM Sales.Sa'.

But as mentioned, in some cases the procedure will not generate an error but rather just apply logic to figure out how to resolve mismatches. For example, if between two corresponding columns one is NULLable and one isn’t, the output will show the column as allowing NULLs. If the column names of corresponding columns are different, the result will have a NULL in the column name.

When existing metadata for a result column is not available from the system catalog, e.g., when the column is based on an expression made of literals, sp_describe_first_result_set will still generate metadata. As an example, here’s a query that returns four columns all based on literals:

EXEC sys.sp_describe_first_result_set
  @tsql = N'SELECT ''abc'' AS col1, 1 AS col2, 12.3 AS col3, 3000000000;';

-- abbreviated output
is_hidden column_ordinal name  is_nullable system_type_id 
--------- -------------- ----- ----------- -------------- 
0         1              col1  0           167            
0         2              col2  0           56             
0         3              col3  0           108            
0         4              NULL  0           108            

system_type_name  max_length precision scale collation_name
----------------- ---------- --------- ----- -------------------------
varchar(3)        3          0         0     Latin1_General_100_CS_AS
int               4          10        0     NULL
numeric(3,1)      5          3         1     NULL
numeric(10,0)     9          10        0     NULL

Interesting to see that the procedure uses a minimalistic approach in terms of maximum length, precision, scale, etc.

SQL Server Denali also provides a procedure called dm_exec_describe_first_result_set_for_object that provides metadata description for a stored procedure or trigger whose id is given as input. As an example, run the following code to create a stored procedure called GetOrderInfo:

IF OBJECT_ID('dbo.GetOrderInfo', 'P') IS NOT NULL DROP PROC dbo.GetOrderInfo;
GO

CREATE PROC dbo.GetOrderInfo
  @orderid AS INT
AS

SELECT OrderDate, TotalDue, CurrencyRateID
FROM Sales.SalesOrderHeader
WHERE SalesOrderID = @orderid;
GO

Then use the following code to describe the GetOrderInfo procedure’s metadata:

SELECT * 
FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('dbo.GetOrderInfo'), 1) ;

Finally, SQL Server Denali also introduces a stored procedure called sp_describe_undeclared_parameters that tries to guess metadata information of undeclared parameters in the code based on context. Here’s an example for using this procedure:

EXEC sys.sp_describe_undeclared_parameters
  @tsql = N'SELECT OrderDate, TotalDue
            FROM Sales.SalesOrderHeader 
            WHERE SalesOrderNumber = @SalesOrderNumber;';

parameter_ordinal name               suggested_system_type_name  
----------------- ------------------ --------------------------- 
1                 @SalesOrderNumber  nvarchar(25)                

suggested_max_length suggested_precision suggested_scale 
-------------------- ------------------- --------------- 
50                   0                   0               

suggested_user_type_id suggested_user_type_database 
---------------------- ---------------------------- 
NULL                   NULL                         

suggested_user_type_schema suggested_user_type_name 
-------------------------- ------------------------ 
NULL                       NULL                     

suggested_assembly_qualified_type_name suggested_xml_collection_id 
-------------------------------------- --------------------------- 
NULL                                   NULL                        

suggested_xml_collection_database suggested_xml_collection_schema 
--------------------------------- ------------------------------- 
NULL                              NULL                            

suggested_xml_collection_name suggested_is_xml_document 
----------------------------- ------------------------- 
NULL                          0                         

suggested_is_case_sensitive suggested_is_fixed_length_clr_type suggested_is_input 
--------------------------- ---------------------------------- ------------------ 
1                           0                                  1                  

suggested_is_output formal_parameter_name suggested_tds_type_id suggested_tds_length
------------------- --------------------- --------------------- --------------------
0                   NULL                  231                   50

As you can see, the procedure figured out the parameter’s metadata based on context—the predicate in the query’s filter that compares the SalesOrderNumber attribute with the @SalesOrderNumber parameter.

For more information about metadata browsing, check out SQL Server Denali’s

Cheers,

BG