few recordsets from a SP - 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

Transact-SQL (2000)

few recordsets from a SP

Author

Topic

yossibaram
Yak Posting Veteran

82 Posts

Posted - 2002-08-15 : 05:04:53

Hi,
Is it possible to retrieve few recordsets from a SP?
Is it possible to create a parameter that will act as a recordset and send it as an Out parameter?
thanks
Yossi

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-08-15 : 05:46:37

re question 1....i think yes....but you need to use a feature/property of recordsets called .nextrecordset...there's been some mention of this here before...a 'forum search' may identify the relevent discussions.

re question 2....i don't understand what you're trying to achieve....
does the standard method of calling a SP to return a recordset not suffice?

if you want to pass the recordset into another SP....probably best to save it in a table....(some) temp tables can be shared between SP's....depending on their scope....

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-08-17 : 23:28:43

Shalom!
You can return several recordsets from an SP, but not as output parameters, which in SQL 7 can be only simple data types, dunno about SQL 2000. Just have several SELECT statements in a row, and SET NOCOUNT ON at the top of the procedure, before the SELECTs. Otherwise, ADO will only return the data from the first statement.

Sarah Berger MCSD

Nico
Starting Member

18 Posts

Posted - 2002-08-19 : 10:21:14

Re 1:
Two ways to solve this

CREATE PROCEDURE sp_Example
AS
SELECT TOP 10 *
FROM tbl

or

CREATE PROCEDURE sp_Example
AS
SET ROWCOUNT 10
SELECT *
FROM tbl

Also, some programming languages may have functions that only retrieve the first row, or first cell. For instance, C# uses ExecuteScalar to retrieve the first cell of the first row.

Re 2:
With the return statement you can pass output parameters.
For example: RETURN @@IDENTITY to retrieve the id of a new record, or RETURN -1 if you're returning an errorcode.
I don't know if return also works for complete recordsets.

Kind regards,
Nico R.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-19 : 10:46:55

did I just see someone post "Shalom!" ?!

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-08-19 : 21:39:20

Seems like you did. You got a problem with that?