Multiple result sets from a stored procedure
I have a stored procedure like the following. This returns 2 result sets, however i only want it to return 2nd (SELECT SomeFieldFROM SomeTable). How is this done? Note - it is not possible to change 'SomeSPThatReturnsAnIntAndAResultSet '
CREATEPROCEDURE [dbo].[SomeSP]
@SomeParamint
AS
BEGIN
SETNOCOUNTON;
declare @SomeScalarint
exec @SomeScalar= SomeSPThatReturnsAnIntAndAResultSet @SomeParam
if @SomeScalar= 0
BEGIN
SELECT SomeFieldFROM SomeTable
END
END
[953 byte] By [
TimSnow] at [2007-12-24]
Tim:
I assume that the internal stored procedure always returns a result set. If that is true, try absorbing the results of that stored procedure into a temp table such as:
insert into #absorbingTempTable
exec @someScalar = SomeSPThatReturnsAnIntAndAResultSet @someParam
With this result absorbed your outer stored procedure should now return only the desired output.
Dave
Yes, this works. Thank you for your response. However, as I understand this requires me to define the table, along with it's structure? Is there an alternative way, as it results in maintenance overhead?
Thanks
Tim,
Could you alter your internal stored proc to do the insert into the temp table and therefore only return a success value?
I'm presuming your internal stored proc looks something like
Select
Col1,
Col2
From
TAB_A
If you changed this to
Select
Col1,
Col2
Into ##TEMPTAB
From
TAB_A
it shouldn't return a result set that would show up in the outer query and the global temp table would be auto-generated and destroyed each time you ran the proc.
HTH
Nick
Hi,
If you just fill in data with your stored procedure, there should be no result passed back. Could you post your inner and outer complete procedure to see what you want to achieve and what the actual problem is ?
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
A very common way is to add a parameter to decide whether or not to execute a statement.
create procedure testMultiSet
(
@resultSet1 bit = 1,
@resultSet2 bit = 1
) as
begin
if @resultSet1 = 1
select 'hi'
if @resultSet2 = 1
select 'there'
end
go
Note that you might want to make the different result sets into their own stored procedures (if possible) for performance reasons. Code not called could have issues with performance of stored plans...
