sql-serverstored-procedurescursors

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!


Solution

  • 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?