How to suppress result sets returned from a stored procedure?

How to suppress result sets returned from a stored procedure?

• gtimofte

  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.

  
  

• Aleniko29139

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

  
  
• How to suppress result sets returned from a stored procedure?