Stored proc columns - 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)

Stored proc columns

Author

Topic

starcem
Starting Member

3 Posts

Posted - 2005-08-18 : 06:05:38

Hi

In SQL Server 2000 how can I return the columns that are output from a stored procedure. I use the sp as input to a Crystal Report but need to provide selection parameters (within the application) based on report columns before invoking the report.

For example, from the stored procedure shown below I would want to know that the colums names are

Firstname, Surname, Birthdate, DateNextBirthday, AgeNextBirthday.

I can’t find a system stored procedure that will produce what I want.

Hoping someone can help.

CREATE PROCEDURE [dbo].[Birthdays] AS
select Firstname, Surname, Birthdate,
(select dbo.GetDateNextBirthday(Birthdate, getdate())) as DateNextBirthday,
(select dbo.GetAgeNextBirthday(Birthdate, getdate())) as AgeNextBirthday
from MEMBER
where Birthdate IS NOT NULL
GO

Kristen
Test

22859 Posts

Posted - 2005-08-18 : 06:40:08

http://sqlteam.com/forums/topic.asp?TOPIC_ID=36396

Plus I'm sure that "one of the regulars here" developed something very clever that would deduce the column names of the result set, but I'm blowed if I can find it here :-(

Maybe they will drop by and remember what the topic was

Kristen

starcem
Starting Member

3 Posts

Posted - 2005-08-21 : 21:26:24

Hi Kristen

I know it can be done because Crystal Reports does it in designer mode.

I've had a look at the link you supplied, which does offer a solution though not too straightforward. Failing anything else arising out of the blue, it'll give it a try.

Thanks
starcem

Kristen
Test

22859 Posts

Posted - 2005-08-22 : 14:48:01

I wish I could remember whom of the "regulars" posted their crafty solutions. I'll ask around

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-22 : 15:11:11

Here's my hack...I did it for bcp, but you could easily use it in a sproc

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Kristen
Test

22859 Posts

Posted - 2005-08-22 : 15:43:46

"Here's my hack"

Where where where where where ????

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-22 : 15:55:14

[d'oh]
http://weblogs.sqlteam.com/brettk/archive/2005/04/13/4395.aspx
[d'oh]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

SamC
White Water Yakist

3467 Posts

Posted - 2005-08-22 : 17:04:21

quote:
Originally posted by X002548

[d'oh]
http://weblogs.sqlteam.com/brettk/archive/2005/04/13/4395.aspx
[d'oh]

Brett... the ending element should be [/d'oh]

HTH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-23 : 00:47:37

This also will give you sp columns

Select parameter_name from information_Schema.PARAMETERS
where specific_name='yourSP'

Madhivanan

Failing to plan is Planning to fail

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-23 : 14:41:22

[code]USE pubs
GO

/* Just a dummy test proc */
/* Returns 2 columns Col1, Col2 */
CREATE PROCEDURE spTest @prmtest1 INT, @prmtest2 INT AS
SET NOCOUNT ON
SELECT name AS Col1, Id AS Col2 FROM dbo.syscolumns
GO

-- SET FMTONLY ON // gets metadata, works with simple procs ( not complex with temporary tables e.g )
SELECT * INTO # FROM OPENQUERY(<LOCALSERVER>, 'SET FMTONLY ON; EXEC pubs..spTest NULL, NULL; SET FMTONLY OFF')
-- change <LOCALSERVER> to Your server, has to be configured for data Access, (You can set that with sp_serveroption)

-- We have column metadata from the proc available
--SELECT * FROM tempdb..syscolumns WHERE id = OBJECT_ID('tempdb..#')
SELECT c.* FROM tempdb.INFORMATION_SCHEMA.COLUMNS c
JOIN tempdb..sysobjects o ON c.TABLE_NAME = o.name AND o.id = OBJECT_ID('tempdb..#')

DROP TABLE #

DROP PROCEDURE spTest
GO[/code]

From ADO / ADO.NET, You could use SET FMTONLY ON on the connection where You execute the sproc.
The returned "table" (recordset,dataset,datareader,datatable...) can be queried for the column metadata.

rockmoose

Kristen
Test

22859 Posts

Posted - 2005-08-24 : 08:00:11

THAT's what I was thinking of, thanks Rocky

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-24 : 10:37:44

No problemo, glad to be of assistance.
Just threading the old path of the masters...

rockmoose

starcem
Starting Member

3 Posts

Posted - 2005-08-25 : 00:54:03

Thanks all for your help. Very much appreciated.

I ended up sourcing quite a simple solution along the lines of what rockmoose suggested. It works a treat:

USE MEMBER
GO
DROP TABLE #tempmemb
SELECT DISTINCT * INTO #tempmemb
FROM OPENROWSET('SQLOLEDB','<servername>';'<username>';'<password>','exec member.dbo.Birthdays')
SELECT DISTINCT column_name FROM tempdb.information_schema.columns WHERE table_name LIKE '#tempmemb___%'
ORDER BY column_name

starcem

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources