Asked  10 Months ago    Answers:  5   Viewed   10 times

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!

 Answers

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?

Monday, August 9, 2021
 
3

If your stored procedure does not return anything you have to use SET NOCOUNT ON;

You can read more about it here:

https://msdn.microsoft.com/en-us/library/ms189837.aspx

Saturday, May 29, 2021
 
samrap
 
5

You can try something like this:

/* Assume this table matches the output of your procedure */
DECLARE @tmpNewValue TABLE (newvalue int)
INSERT INTO @tmpNewValue 
EXEC ProcedureB
Wednesday, August 4, 2021
4

Try altering the stored procedure in a session after setting ANSI_NULLS to ON.

SET ANSI_NULLS ON
GO

alter procedure procedureName 
...

From the docs:

For stored procedures, SQL Server uses the SET ANSI_NULLS setting value from the initial creation time of the stored procedure. Whenever the stored procedure is subsequently executed, the setting of SET ANSI_NULLS is restored to its originally used value and takes effect. When invoked inside a stored procedure, the setting of SET ANSI_NULLS is not changed.

Saturday, August 14, 2021
 
2

Any ideas why this would happen?

Compilation.

The master script likely is compiled batch by batch using the statistics valid at this point.

The SP will be compiled once at start, and if the statistics change during the run - as is typial for a sequence of loads - there you go. Especially if the statistical change is significant during processing. Basically the stats at teh beginning - when things are compiled - are totally off compared to the runtime stats for some tables.

There is a recompile option that you can se tin the individual statements to avoid this.

Wednesday, December 8, 2021
 
Asperi
 
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share