Returning ROWSET from a stored proc

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)

Returning ROWSET from a stored proc

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-06-07 : 08:24:55

Kristian writes "I have see/used syntax for returning a cursor from a stored procedure and this is useful when the volume of data is large.

However, I can't find the syntax for SQL server to return a rowset from a stored proc. I need to pass a small number of rows and all the data at the same time.

It it possible with SQL server.

It would look something like this:

CREATE PROCEDURE [ROUTING].[GetRoutingTable]
(
@RowCount INT OUTPUT,
@RoutingTable ROWSET OUTPUT
)
AS
BEGIN

SET @RoutingTable = ( SELECT * FROM [ROUTING].ROUTINGRULES );

SET @RowCount = @@ROWCOUNT;
END;
GO

regards
Kris"

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-07 : 08:36:47

CREATE PROCEDURE [ROUTING].[GetRoutingTable]
(
@RowCount INT OUTPUT
)
AS
BEGIN

select @RowCount = count(*() from [ROUTING].ROUTINGRULES
SELECT * FROM [ROUTING].ROUTINGRULES

END;
GO

This will give a resultset which the app can read after executing the sp.
You can also get the rowcount from the resultset rather than passing it as an output parameter.

==========================================
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.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-07 : 08:38:52

If you want to return resultset use

SELECT * FROM [ROUTING].ROUTINGRULES

To return Row count

Select @@Rowcount

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources