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!

Dvandva

Are you really sure you need to use a cursor? Those are not supposed to be the first choice in SQL Server these days. In fact, many experts abhor them. – Meadow

Please post here the error you're receiving. – Heckman

Answer 1

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?

Spevek

I was re-using a proc utilized by the web app for a reusable import procedure, so I couldn't change any behaviour of the proc. This was just the thing. – Whiffen

Answer 2

You can discard the resultsets in SQL Server Mgmt Studio 2005 by following the steps below:

• Right-click in the query window
• Choose "Query Options"
• Click on the "Results" "node" in the left panel tree view
• Check "Discard results after execution" in the center/right of the form

You can try it on

DECLARE @i int
SET @i = 1
WHILE (@i <= 100)
  BEGIN
    SELECT @i as Iteration
    SET @i = @i + 1
  END

Marvellamarvellous

This should be the accepted answer - it's the SSMS option to do exactly what the original poster requested – Scree

Answer 3

I know this question is old, but you could set the SET NOCOUNT ON to prevent the SP to output a message for every row.

Illimani

Thank you, thank you, thank you! This is the exact answer for SQL 2000. I Assume Adam's answer works for SQL 2005+. – Conversant

NOCOUNT is good as it keeps the number of results from being displayed, however the 6eorge Jetson/Robert Koritnik excludes extra result sets from being displayed (which I thought is what the original question was)? – Lilililia

Answer 4

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.

Kinata

You're right - although I'm restricted here. I can't change the stored procedure for support reasons as it belongs to a third party. – Dvandva

Answer 5

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!

Heckman

Answer 6

This page is old and the replies are old. But, the best answer has not been upvoted to the top. I suppose it is because not enough explanation was provided.

Use the NOCOUNT setting. Everyone should look at the NOCOUNT setting. The default setting is OFF.

Changing the default setting of a this universally even on a new database may cause confusion for some coders\users. I recommend using the approach of capturing the setting before changing it, then setting it back. This is shown in the example script below which demonstrates use of the NOCOUNT setting.

Here is a good article. https://www.sqlshack.com/set-nocount-on-statement-usage-and-performance-benefits-in-sql-server/

DROP TABLE IF EXISTS TestTable
GO
CREATE TABLE TestTable (ID INT, TestText VARCHAR (40))
GO

-- Get the Original NOCOUNT setting and save it to @OriginalNoCountSettingIsOn
DECLARE @options INT
SET @options = @@OPTIONS
DECLARE @OriginalNoCountSettingIsOn AS bit
SET @OriginalNoCountSettingIsOn = IIF(( (512 & @@OPTIONS) = 512 ),1,0)

-- Now set NOCOUNT ON to suppress result output returned from INSERTS 
-- Note - this does not affect @@ROWCOUNT values from being set 
SET NOCOUNT ON --  <---- Try running script with SET NOCOUNT ON and SET NOCOUNT OFF to See difference

INSERT INTO TestTable (ID, TestText)
VALUES  (0, 'Test Row 1')

INSERT INTO TestTable (ID, TestText)
VALUES  (0, 'Test Row 2'),
        (0, 'Test Row 3'),
        (0, 'Test Row 4')

INSERT INTO TestTable (ID, TestText)
VALUES  (0, 'Test Row 5')

/*Now set NOCOUNT back to original setting*/
IF @OriginalNoCountSettingIsOn = 1 
BEGIN
    SET NOCOUNT ON
END
ELSE
BEGIN
    SET NOCOUNT OFF
END 

DROP TABLE IF EXISTS TestTable
GO

Abubekr

Answer 7

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.

Wiener

I tried it but received an error. I also tried wrapping it around SET ROWCOUNT 1 and SET ROWCOUNT 0 without success. – Dvandva

I believe this works for SQL Server 2005 and up. If you are using SQL 2000, see Estanislao' response. – Conversant

The syntax for SET ROWCOUNT is SET ROWCOUNT {number|@number_var}. It does not take ON/OFF as the argument. – Mendelian