Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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!

like image 279
Alex Angas Avatar asked Oct 17 '08 15:10

Alex Angas


People also ask

How do you suppress the select output of a stored procedure called from another stored procedure in SQL Server?

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

Can you execute a stored procedure in a select statement?

We can not directly use stored procedures in a SELECT statement.

How do I restrict access to stored procedure in SQL Server?

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.


1 Answers

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 

like image 59
6eorge Jetson Avatar answered Sep 24 '22 05:09

6eorge Jetson