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 ofprocA 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?
You could use an output variable with a concatenation of the results (something like: 'count1, count2, count3') - it is not very pretty, but it will probably work...
The inner resultsets wil be returned to the client (even if it is not using them), so be carefull because you might be sending lots information you don't need through your network.
Also, if you end up using the concatenation/output variable you shouldn't use ExecuteReader but ExecuteNonQuery instead, so that your sqlclient doesn't need to process the results.
|||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?
>
>
>
>
>
>
>
>
>
>
No comments:
Post a Comment