multiple result sets from stored proc

Hot Topic

MySQL help in C++
URL rending vs embedded reports
problem to set properties on a subfolder if no access to the home folder
MgmtStudio: Use current connection/window when opening file
mysql limit equivalent
The Trash Destination Adapter - Really needed?
Custom aggregate function
SQL Server 2005 SP1 Webchat - March 22 at 9:00 am PST
How do I read inserted text data in a trigger?
SQL Server 2005 Service Pack 1 public (NON-CTP) is available

SQL Server

transaction isolation question
Upgrading 32 bit SQL 2000 SP4 Ent running on 64 bit Windows 2003 Enterprise to SQL2005 64 bit in place
Problem installing SQL Server 2005 Express with Advance Services CTP
Network access to Sql Server 2005 Express
how to get the status of update sucessing?
How to check if the file exists or not before bulk insert
Common Task succeeds but reports failure
rsInvalidReportServerDatabase
OK button not working...
Unable to open MSDB node

multiple result sets from stored proc

I have a problem with the following query analyzer code.

exec RoomRoster '3/9/2006',5,1,60,null,null,null
select
rr.*,
d.dependent_name
from
##RoomRoster rr
inner join cm_dependents d
on d.dependent_contract_fk = rr.cntrWRL_contract_fk
drop table ##RoomRoster

This t-sql calls a SP that returns a global temporary table named ##RoomRoster.

This sql produces 2 result sets in 2 QA grids. The first is ##RoomRoster and the second is the results of the sql that I need to work with.

I want to produce a single result set, the second one and delete, remove, eliminate or whatever it takes to suppress the first from being returned from this procedure.

This sql runs without error.

I am stumped, please help!

Sincerely Mark Atherton

Answer this question

multiple result sets from stored proc

DavidHarrison
Just isnert results of stored procedure into temporary table (using insert into ... exec statement), then append there your second select (insert into .. select) and select data to the client at the end.
DavideA

I would reccomend changinf you roomroster SP to not return the results from ##roomroster, the could be implemented directly or optionally based on a parameter passed in. If th default is to return the results then existing code won't break, but will allow this SP to not have the results returned to the client.
multiple result sets from stored proc

Answer this question