Retrieve data types of the selected columns

Question

Is there a way to retrieve the data types of selected columns from a stored procedure in SQL Server.

E.g.

ALTER PROCEDURE [Product].[usp_ProductType_Get]
AS
BEGIN
  SELECT ID,Description
  FROM Product.Product
END 

Need to retrieve as below.

Column        DataType
ID             INT
Description    VARCHAR

Answer 1

As this is an SP you can use sp_describe_first_result_set (Transact-SQL):

EXEC sys.sp_describe_first_result_set N'EXEC [Product].[usp_ProductType_Get];';

If you only want specific columns, you'll need to insert the data from the EXEC into a temporary table, and then select those tables. (will post how to do so shortly).

Edit: Using the linked documentation to design a temporary table:

CREATE TABLE #FirstResultSet (is_hidden bit NOT NULL,
                              column_ordinal int NOT NULL,
                              name sysname NULL,
                              is_nullable bit NOT NULL,
                              system_type_id int NOT NULL,
                              system_type_name nvarchar(256) NULL,
                              max_length smallint NOT NULL,
                              precision tinyint NOT NULL,
                              scale tinyint NOT NULL,
                              collation_name sysname NULL,
                              user_type_id int NULL,
                              user_type_database sysname NULL,
                              user_type_schema sysname NULL,
                              user_type_name sysname NULL,
                              assembly_qualified_type_name nvarchar(4000),
                              xml_collection_id int NULL,
                              xml_collection_database sysname NULL,
                              xml_collection_schema sysname NULL,
                              xml_collection_name sysname NULL,
                              is_xml_document bit NOT NULL,
                              is_case_sensitive bit NOT NULL,
                              is_fixed_length_clr_type bit NOT NULL,
                              source_server sysname NULL,
                              source_database sysname NULL,
                              source_schema sysname NULL,
                              source_table sysname NULL,
                              source_column sysname NULL,
                              is_identity_column bit NULL,
                              is_part_of_unique_key bit NULL,
                              is_updateable bit NULL,
                              is_computed_column bit NULL,
                              is_sparse_column_set bit NULL,
                              ordinal_in_order_by_list smallint NULL,
                              order_by_list_length smallint NULL,
                              order_by_is_descending smallint NULL,
                              tds_type_id int NOT NULL,
                              tds_length int NOT NULL,
                              tds_collation_id int NULL,
                              tds_collation_sort_id tinyint NULL);
INSERT INTO #FirstResultSet
EXEC sys.sp_describe_first_result_set N'SELECT 1 AS one;';

SELECT [name] AS [Column],
       system_type_name AS DataType
FROM #FirstResultSet;

DROP TABLE #FirstResultSet;

Answer 2

Yes, it is possible, you can use the below query. You need to pass the table name and columns name as per your requirement. It will return the column name along with the datatype

SELECT COLUMN_NAME,DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
     TABLE_NAME = 'YourTableName' AND 
     COLUMN_NAME in( 'YourColumnName1', 'YourColumnName2')

The below Proecure will take table name as parameters and will return all the columns name along with the datatype

Create Proc GetColumns (@tablename varchar(100)) 
as
Begin
SELECT COLUMN_NAME,DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
     TABLE_NAME =  @tablename
End
go

Exec GetColumns 'YourTableName'