T-SQL 2005：抑制结果集的输出

I have some stored procedures which are used for generating Reports. I am trying to build a report dashboard that will show how many records are on each report.

我有一些存储过程用于生成报告。我正在尝试构建一个报告仪表板,它将显示每个报告上有多少条记录。

The SPs are detailed in a table which details in which order they should be run.

SP详细列在一个表格中,详细说明了它们的运行顺序。

I have a dashboard SP in which I am using Cursor to go through the database table, execute each report SP and then saving the @@rowcount into a temp table which forms the basis of the dashboard.

我有一个仪表板SP,我在其中使用Cursor浏览数据库表,执行每个报告SP,然后将@@ rowcount保存到临时表中,该临时表构成仪表板的基础。

I can turn off the row count using set rowcoun off at the top of each SP, but I can't find a way to stop the results set being retured from the EXEC sp_my_sp command.

我可以使用每个SP顶部的set rowcoun off关闭行计数,但是我找不到停止从EXEC sp_my_sp命令恢复结果集的方法。

WHILE @@FETCH_STATUS = 0
BEGIN
    declare @x as int
    exec @SP_name @x OUT
    select @x
    insert into @results (Short___name,Records) values (@shortname,@x)
    FETCH NEXT FROM myCursor INTO @ShortName, @SP_Name,@Group,@Order
END

Somewhere in there I need to suppress or redirect the output. I don't want to do it inside the actual report SP.

在那里的某个地方,我需要抑制或重定向输出。我不想在实际报告SP中这样做。

The only solution I have come up with so far is to add an input parameter to each SP to say whether it is being run for the dashboard or for the report. Instead of returning the results set directly, insert it into an in-memory table and and then report the in-memory table ONLY if we are running for reports - not nice as someone has to remember to do it for each new SP

到目前为止,我提出的唯一解决方案是向每个SP添加一个输入参数,以说明它是为仪表板还是为报告运行。而不是直接返回结果集,而是将其插入到内存表中,然后仅在我们运行报告时报告内存中的表 - 不好,因为有人必须记住为每个新SP执行此操作

TIA

Obiron

1 个解决方案

#1

I can think of one possible solution but I'm not sure if it's particularly elegant (or what you're looking for).

我可以想到一个可能的解决方案,但我不确定它是否特别优雅(或者你正在寻找什么)。

Expanding on the idea of an input parameter to the SP, could you not just add some logic (CASE or IF statement) to call a Select Count() over the script when it comes from the dashboard?

扩展到SP的输入参数的概念,你可以不只是添加一些逻辑(CASE或IF语句)来调用来自仪表板的脚本上的Select Count()吗?

It would need adding to each SP but as long as it's documented it shouldn't cause too many problems for future developers.

它需要添加到每个SP,但只要它被记录,它不应该为未来的开发人员造成太多问题。

#1