Result Set Column Names - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Result Set Column Names

Author

Topic

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2003-05-12 : 15:38:45

Is it possible to retrieve the name of the columns in a result set
from a sproc?

Basically, if I have a sproc that :
Select beerid,beername,beercountry ,rating
from MyFavoriteTable
where rating > 2

Is there anyway to know that the fields coming out are those in the select
via a sql server? (So I could effectively create a generic inteface to my front end code).

Thanks...

________________________________________________
Everytime I think I understand women, I leave the tavern and go home to be humbled.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-12 : 15:48:14

I don't know about a generic interface...don't you need to worry about number of columns and data types...

Anyway...

SELECT 'col1','col2','col3'
UNION ALL
SELECT CharCol, Convert(varchar(26),dteCol), Convert(varchar(15),intCol) FROM your Table

Brett

8-)

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2003-05-12 : 15:54:40

well,

my thinking is this.

Can I say, what are the column names of the result set
generated by spXYZ?

________________________________________________
Everytime I think I understand women, I leave the tavern and go home to be humbled.

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-05-12 : 22:20:18

Huh?
What tool are you using to transport your data from the stored proc to the front end?
'Cuz if you are using ADO, rs.field(index).name will give you all the column names. You can even get the column types, column lengths,etc.
Please explain your situation in more depth.

Sarah Berger MCSD

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-13 : 10:26:12

You can get the column names but I wouldn't advise it.

You can use openquery to run the sp and create a temp table from it using select .. into...
You can then get the column names from that temp table.
select name from tempdb..syscolumns where id = object_id('tempdb..#tbl')

Repeat - I wouldn't advise it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2003-05-14 : 08:36:59

Thanks,

I see that my plan could work, but really isn't "feasible"

I had been trying to figure out how a data reader could
the column names, but I couldn't find where sql server would
hold those names.

Thanks for the insight.

________________________________________________
Everytime I think I understand women, I leave the tavern and go home to be humbled.

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-14 : 08:50:50

If you were trying to get the column names in ASP, ASP/ADO has a method to retrieve column names.

For each rsobj in rs.fields
response.write rsobj.name & " = " & rsobj.value & "<br>"
Next

Sam

gvphubli
Yak Posting Veteran

54 Posts

Posted - 2003-05-15 : 06:34:48

quote:
For each rsobj in rs.fields
response.write rsobj.name & " = " & rsobj.value & "<br>"
Next

YES if ur using some front end like VB or ASP...this can work...else just the sp will not return what ur asking..when u have written it for executing for someother query.

- = Cracky DBA = -
http://www.geocities.com/gvphubli/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources