I ran into this issue as well.

Why would you want to know what the data types are going to be coming out of a stored procedure?

Well I am providing services to a larger programming group. They are creating C# objects to hold the data that I am going to pass them however, in my current situation, there is a stored procedure that they are going to need the result set from and as a result would like to know what the field types and max length are going to be .

The stored procedure return comes from a rather complex set of joins , so while I could go and chase down every field and its type/length it would be nicer if I could simply get that on the results end.

So here is the solution I came up with. Part of it was from the above posts, other parts are from other sites. Hope this helps.

First I used OPENROWSET to execute and return the results from the stored procedure into a temporary table.

Of course you're going to want to put in your server info and the stored proceudre that you're trying to evaluate.

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=SQLMYSERVER\pubweb;Trusted_Connection=yes;',

'EXEC [PrcThatProcedure] ''SomeValuePassed'', 1 ')

Next, you run this script, similar to a previously posted script but with the extra case statement to better label the data type.

select

CASE

WHEN c.system_type_id = 34 THEN 'byte[]'

WHEN c.system_type_id = 35 THEN 'string'

WHEN c.system_type_id = 36 THEN 'System.Guid'

WHEN c.system_type_id = 48 THEN 'byte'

WHEN c.system_type_id = 52 THEN 'short'

WHEN c.system_type_id = 56 THEN 'int'

WHEN c.system_type_id = 58 THEN 'System.DateTime'

WHEN c.system_type_id = 59 THEN 'float'

WHEN c.system_type_id = 60 THEN 'decimal'

WHEN c.system_type_id = 61 THEN 'System.DateTime'

WHEN c.system_type_id = 62 THEN 'double'

WHEN c.system_type_id = 98 THEN 'object'

WHEN c.system_type_id = 99 THEN 'string'

WHEN c.system_type_id = 104 THEN 'bool'

WHEN c.system_type_id = 106 THEN 'decimal'

WHEN c.system_type_id = 108 THEN 'decimal'

WHEN c.system_type_id = 122 THEN 'decimal'

WHEN c.system_type_id = 127 THEN 'long'

WHEN c.system_type_id = 165 THEN 'byte[]'

WHEN c.system_type_id = 167 THEN 'string'

WHEN c.system_type_id = 173 THEN 'byte[]'

WHEN c.system_type_id = 175 THEN 'string'

WHEN c.system_type_id = 189 THEN 'long'

WHEN c.system_type_id = 231 THEN 'string'

WHEN c.system_type_id = 239 THEN 'string'

WHEN c.system_type_id = 241 THEN 'string'

WHEN c.system_type_id = 241 THEN 'string'

END AS [Type]

, c.*

from tempdb.sys.columns c

where c.object_id = (select top 1 t.object_id

from tempdb.sys.tables t

where t.name like '#MyTempTable%')

Hope this helps.

~Wade Cantley