Stored Procedure Result Set Column Definitions

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 Procedure Result Set Column Definitions

Author

Topic

Jes350
Starting Member

2 Posts

Posted - 2002-05-09 : 14:24:41

Is there a way to get the column definition for the result set of a stored procedure without executing the stored procedure?

sp_sproc_columns looks like it will do it, but the documentation states, "In SQL Server, only the column information about input and output parameters for the stored procedure are returned."

Any help would be greatly appreciated.

Thanks,
Eric

dsdeming

479 Posts

Posted - 2002-05-09 : 14:49:52

Stored procedures can return one or more sets of data in addition to a return value. As far as I know, there is no system procedure that will give you the column definitions for the data sets being returned. If you're using ADO, you should be able to get the info you want after you execute the procedure.

Jes350
Starting Member

2 Posts

Posted - 2002-05-09 : 17:11:42

I'm assuming the procedure only returns one result set. I need to obtain the result set column definitions without executing the stored procedure.

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-05-09 : 18:54:00

Can you execute the stored proc, even if it return data?

You stored proc looks something like this:

<Psudo code>
@RecordID uniqueidentifier,
@GetData bit

SELECT field1, field2 FROM table
WHERE 1=0

If @GetData = 1
BEGIN
SELECT field1, field2 FROM table
WHERE recordID = @RecordID

END
</Psudo code>

Since this will give you "two recordsets" you'll need to use oRecordSet.NextRecordSet to get your data, if you set GetData = 1
If GetData != 1 then you'll get an empty recordset, but you should be able to use the recordset object to get all the field definitions etc. with oRecordSet.Fields.

HTH!
Michael

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-05-10 : 14:20:40

What are you doing with the information when you retrieve it? If you need it, for example, to build parameters for a command object, then you could just use the cmd.Parameters.Refresh method.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources