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

avatar image

jjkh
11 1 1 1

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

2 answers: sort voted first

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

avatar image

Kev Riley ♦♦
63.8k 48 61 81

(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

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(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.

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:

x454

asked: Apr 14, 2011 at 02:57 AM

Seen: 2696 times

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

Copyright 2016 Redgate Software. Privacy Policy