How to suppress result sets returned from a stored procedure?

I may not completely understand your goal here, but see

if this is any help. Here, p1 and p2 are procedures that

return a single rowset. The procedure p3 returns as a single

row the total number of rows returned by p1 and p2. This

will only work if p1 and p2 don't have parameters that

change. If they do, there are other less convenient

solutions, though.

(ME must be defined as a linked server to do this)

create proc p1 as

set nocount on

select top (5) percent OrderID

from Northwind..Orders

go

create proc p2 as

set nocount on

select top (5) percent OrderID

from Northwind..[Order Details]

go

create proc p3 as

set nocount on

declare @i int

set @i = (

select count(*) from openquery(ME,'p1')

)

set @i = @i + (

select count(*) from openquery(ME,'p2')

)

select @i as TotalCount

go

exec p3

go

drop proc p3, p2, p1

Steve Kass

Drew University

www.stevekass.com

Finkster@discussions.microsoft.com wrote:

> I have a novice question. How does one suppress result sets returned

> from a stored procedure?

>

>

>

> I have created a procedure which makes use of multiple stored procedures

> . The purpose of this procedure (lets call it procA), is to count the

> rows returned from other procedures. The “Other” procedures will return

> rows having an unknown number of columns. I would like to limit any

> changes which may be needed to be made to the “Other” procs.

>

>

>

> Once procA has collected all of the information (@@rowcount) from the

> inner procedures, then it will return a result set having several

> columns – mainly the subProcedure name and number of rows returned.

>

>

>

> The purpose of procA is to query several subsystems and identify which

> ones need attention.

>

>

>

> Cursor While Loop

>

> exec @ProcName @ObjectName,@userID,@syncDate

>

> set @recs = @@rowcount;

>

>

>

>

>

> My c# program calls the sp as follows:

>

>

>

> cmd = DataUtility.GetSQLCommand();

>

> cmd.CommandType = CommandType.StoredProcedure;

>

> cmd.CommandText = "FetchAdminData";

>

> cmd.Parameters.AddWithValue("@userID", userAlias);

>

> cmd.Parameters.AddWithValue("@adminDate",userDate);

>

> reader = cmd.ExecuteReader();

>

>

>

> I do not wish to iterate over each resultSet when I am only interested

> in the last one. Any suggestions?

>

>

>

>

>

>

>

>

>

>