Does anyone know of or have a routine to output a list of column names returned by a stored procedure? For instance if the procedure contains a select col1, col2, col3 from table1 then the returned list would be col1, col2, col3
asked Apr 14, 2011 at 02:57 AM in Default
Not inbuilt within SQL Server I don't, but certainly you could write something in .Net (for example) that would give you the 'signature' of the proc.
This obviously only works if the output isn't dynamic :
is a valid (albeit horrendous) stored proc, but the output is determined by the value of the input.
If you have a stored procedure which returns a single, deteministic result set, then you can use a CLR object, and run T-SQL code like:
You could then work out the result set shape from the metadata returned, and return it as a list.
However, result sets that change shape (e.g. a dynamic pivot), result sets that appear or disappear based on input (think Kev's example) are dangerous territory when using that approach.
answered Apr 14, 2011 at 03:42 AM
Matt Whitfield ♦♦