How can I get rs cols which a select sp returns

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

Transact-SQL (2000)

How can I get rs cols which a select sp returns

Author

Topic

draduc
Starting Member

4 Posts

Posted - 2005-05-03 : 04:48:45

There is any way to get the recordset columns which a select stored procedure returns?
The solution I implemented is to exec the sp and get the columns from the datatable (ADO.NET). I think should be other way to get the columns (sql stuff).
Cheers,
Dragos

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-05-03 : 05:11:07

you need to use output parameter in the Stored Procedure

Madhivanan

Failing to plan is Planning to fail

draduc
Starting Member

4 Posts

Posted - 2005-05-04 : 10:33:19

what I mean

I have the following table
create table t1(c1 int, c2 int, c3 varchar(50))

and the following stored procedure
create proc t1_Sel
as
select * from t1

I need a way to get the columns t1_Sel returns.
In this case the result would be:
c1
c2
c3

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-05-04 : 23:25:03

quote:
Originally posted by draduc

what I mean

I have the following table
create table t1(c1 int, c2 int, c3 varchar(50))

and the following stored procedure
create proc t1_Sel
as
select * from t1

I need a way to get the columns t1_Sel returns.
In this case the result would be:
c1
c2
c3

select c1 from t1_sel
union
select c2 from t1_sel
union
select c3 from t1_sel

HTH

--------------------
keeping it simple...

draduc
Starting Member

4 Posts

Posted - 2005-05-07 : 17:44:32

I need the column names not the values. I know nothing about table. What I know is the sp name. The above sp is a very simple one. In real life could be many join tables. See VS 2003 database manager. You are able to see the parameters of a sp and also the columns that sp returns.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-05-09 : 00:48:43

If you want to have column names, you can use

create proc t1_Sel
as
Select name from syscolumns where id=object_id('t1')

Madhivanan

Failing to plan is Planning to fail

draduc
Starting Member

4 Posts

Posted - 2005-05-10 : 05:04:15

Hei madhivanan u don't get the idea.

I wanna get the column names at run time from a C# code. For the application the stored procedure is just a black box. The only think I know is the stored procedure name and based on this I'd like to get the column names.
I did this executing the stored procedure and get the columns from datatable Columns collection. But this way is time consuming if u have a big database.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-10 : 07:39:27

>>I did this executing the stored procedure and get the columns from datatable Columns collection. But this way is time consuming if u have a big database.

You should use a datareader -- if "dr" is your datareader object variable, then dr.GetName(x) returns the name for column x. This will be much more efficient than filling up a datatable just to get the columns! You can close the data reader after reading the very first row.

Why do you need to do this? Are you saying you need to do this for various stored procs, but not at the time you execute them and return the data? I'm not sure what you are trying to get at. It might be easier if you tell us what your goal is with all this.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources