x

column names returned by a stored procedure

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
more ▼

asked Apr 14, 2011 at 02:57 AM in Default

jjkh gravatar image

jjkh
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort oldest

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 :

create proc MyProc (@id int)
as
if @id = 1 select * from sys.sysobjects
if @id = 2 select * from sys.sysprocesses
go
is a valid (albeit horrendous) stored proc, but the output is determined by the value of the input.
more ▼

answered Apr 14, 2011 at 03:14 AM

Kev Riley gravatar image

Kev Riley ♦♦
52.7k 47 49 76

(comments are locked)
10|1200 characters needed characters left

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:

SET FMTONLY ON
GO
EXEC MyProc

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.
more ▼

answered Apr 14, 2011 at 03:42 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x407

asked: Apr 14, 2011 at 02:57 AM

Seen: 2061 times

Last Updated: Apr 14, 2011 at 02:57 AM