How do I disable query results when executing a stored procedure from a stored procedure?

Question

Within a stored procedure, another stored procedure is being called within a cursor. For every call, the SQL Management Studio results window is showing a result. The cursor loops over 100 times and at that point the results window gives up with an error. Is there a way I can stop the stored procedure within the cursor from outputting any results?

  WHILE @@FETCH_STATUS = 0
  BEGIN
    EXEC @RC = dbo.NoisyProc
    SELECT @RValue2 = 1 WHERE @@ROWCOUNT = 0
    FETCH NEXT FROM RCursor INTO @RValue1, @RValue2
  END

Thanks!

Answer 1

Place:

SET ROWCOUNT OFF
/* the internal SP */
SET ROWCOUNT ON

wrap that around the internal SP, or you could even do it around the SELECT statement from the originating query, that will prevent results from appearing.

Answer 2

you could insert the results into a temp table, then drop the temp table

create table #tmp (columns)

while
    ...
    insert into #tmp exec @RC=dbo.NoisyProc
    ...
end
drop table #tmp

otherwise, can you modify the proc being called to accept a flag telling it not to output a result-set?

Answer 3

Cursors bad. Don't reuse stored proc code if it means you have to do a set-based function with a cursor. Better for performance to write the code in a set-nbased fashion.

I think I'm concerned that you are more concerned with supressing the messages than you are that you have an error in the cursor.

Answer 4

Probably the error comes from too much recordsets being returned, rather than a logic flaw on your SP or the cursor itself. Look at this example:

DECLARE @I INT
SET @I=0
WHILE @I<200 BEGIN
    SELECT * FROM INFORMATION_SCHEMA.TABLES
    SET @I = @I + 1
END

Will run a number of times (slightly more than 100) then fail with:

The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid.

The SSMS has a limit on the number of record-sets it can show you. One quick way to by-pass that limitation is to press Ctrl+T (or menu Query->Results to->Results to Text) to force the output to be in plain text, rather than table-like recordsets. You'll reach another limit eventually (the results window can't handle an infinite amount of text output) yet it will be far greater.

In the sample above you don't get the error after changing the results to be in text form!