从存储过程执行存储过程时如何禁用查询结果? [英] How do I disable query results when executing a stored procedure from a stored procedure?

问题描述

在一个存储过程中，另一个存储过程在一个游标内被调用.对于每次调用，SQL Management Studio 结果窗口都会显示一个结果.光标循环超过 100 次，此时结果窗口因错误而放弃.有没有办法阻止游标中的存储过程输出任何结果?

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

谢谢！

推荐答案

你可以将结果插入到临时表中，然后删除临时表

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

否则，您能否修改被调用的 proc 以接受一个标志，告诉它不输出结果集?

otherwise, can you modify the proc being called to accept a flag telling it not to output a result-set?

这篇关于从存储过程执行存储过程时如何禁用查询结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！