Conditional Output from Stored Procs

Question

Conditional Output from Stored Procs

Brian Wawrow

SSC Veteran

Points: 266
More actions
June 15, 2005 at 9:40 am

#102591

Hi,
I am putting together some automated reporting based on a list of stored procs listed in a table. I load all the stored proc details into a cursor, cycle through them and run them one at a time. Each stored proc returns a different set of columns and none require any input data.
The trick is this. I only want to see output [including headers and rowcount] when @@ROWCOUNT is greater than 0.
Here's how the cursor looks so far...
FETCH NEXT FROM cIntChk INTO @IntegrityCheckID,
@ErrorNumber,
@ErrorLetter,
@StoredProcedureName,
@StoredProcedureLabel
WHILE(@@FETCH_STATUS = 0)
BEGIN
PRINT @StoredProcedureLabel
-- how can I make the out put from this procedure call
-- conditional on rowcount???
EXEC @StoredProcedureName
FETCH NEXT FROM cIntChk INTO @IntegrityCheckID,
@ErrorNumber,
@ErrorLetter,
@StoredProcedureName,
@StoredProcedureLabel
END
CLOSE cIntChk
DEALLOCATE cIntChk
Cheers!
Brian Wawrow

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Why not build a "test run" parameter in that writes the @@ROWCOUNT to a table along with the SP name and then you can SELECT from there??

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Brian Wawrow SSC Veteran Points: 266 More actions · Answer 2

hmmm... I guess that would work. I was hoping for something a bit smoother but it is an overnight process so I could run some procedures twice.

I would have to do it in two separate jobs and only capture the output from the second one.

Much obliged AJ

Cheers!

Brian Wawrow

Jeff Gray SSChampion Points: 10667 More actions · Answer 3

Brian,

If you want to avoid the computational overhead of running the procedures twice, you may consider using staging tables to hold the results and decide later whether to output them based on the rowcount.

You could:

dynamically create the staging table (based on another column selected in your cursor perhaps)

Insert Into table EXEC storedprocedure

if exists (select * from table)

Select * from table

drop table

rinse, repeat.

This would beg the use of dynamic SQL, and may not be appropriate for your environment. There will be some tweeking for error handling, but this might point you in a suitable direction.

hth

JG