July 1, 2014

Determine the column types returned from a Stored Procedure

Say you have a stored procedure that does something like this:
SELECT A.id, 10.0 * A.number / B.number AS Computed FROM Table1 A JOIN Table2 B on A.id = B.id
What is the resulting type of the "Computed" column? Decimal? Float?
The following SQL 2012 will tell you the data types of the columns from the first returned data set of a stored procedure:
SELECT name, system_type_name
FROM sys.dm_exec_describe_first_result_set_for_object
    (OBJECT_ID('myNamespace.mySPROC')
    NULL
);
This will give a nice table that contains the column name and system data type
name                  system_type_name
--------------------- ----------------------
Id                    bigint
Computed              float