T-SQL 2005: Suppress the output of the results set.

Question

Hi guys.

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.

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.

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.

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.

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

TIA

Obiron

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

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