CODEWITHSUNDEEP
sql-serverstored-procedurescursors
Alex Angas
Alex Angas

Reputation: 60027

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

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!

Upvotes: 25

Views: 62734

Answers (7)

Todd Albers
Todd Albers

Reputation: 119

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

Upvotes: 1

6eorge Jetson
6eorge Jetson

Reputation: 2235

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

Upvotes: 34

Estanislao
Estanislao

Reputation: 121

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.

Upvotes: 12

Steven A. Lowe
Steven A. Lowe

Reputation: 61233

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?

Upvotes: 16

Joe Pineda
Joe Pineda

Reputation: 5661

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!

Upvotes: 1

HLGEM
HLGEM

Reputation: 96562

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.

Upvotes: 3

Adam
Adam

Reputation: 7900

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.

Upvotes: 0

Related Questions