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!
The script result was executed using SSMS and default option on query window is set to show “Results to Grid”[Ctrl+d shortcut]. Easy Solution: Try setting the results to file to avoid the grid to be built and painted on the SSMS client. [CTRL+SHIFT+F keyboard shortcut to set the query results to file].
We can not directly use stored procedures in a SELECT statement.
Use SQL Server Management StudioExpand Stored Procedures, right-click the procedure to grant permissions on, and then select Properties. From Stored Procedure Properties, select the Permissions page. To grant permissions to a user, database role, or application role, select Search.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With