Summary Record Approach For Non Similar Data

[Auguy]

I’m developing a summary report where each line consists of a category and the record count for that category. Each of these categories and the counts is derived from different tables. The user may pick one, some, or all of the categories to include in the report. My first thought is to put the all of the selects for each category into one stored proc with a Union All to get the record counts. I don’t really like this approach because categories could be added to the list and the stored proc could get messy. I’m thinking about having a stored proc for each category and inserting the results directly into a summary table. Then I just select from the summary table as the datasource for the report and delete the summary records when I’m done. Has anybody done anything like this or have a better approach? The number of categories will be less than 20. Using VB.net 2008 and SQL 2005.

Auguy
Northwest Ohio

 

[RiverGuy]

I don't really like that approach. What if two people want to run the report at the same time?

I would use and maintain the UNION ALL approach before I did the INSERT/DELETE method.

One other thing to consider is just using a DataTable for the data source. You can fill it up multiple ways, so you should be able to make multiple calls to multiple stored procedures to end up with a single DataTable.

 

[Auguy]

Thanks RiverGuy. I was going to include some kind of identifying column like UserId or even a unique "Run" number to keep the results separate between users. In regards to your last comment, that was what I meant by "inserting the results directly into a summary table".

Auguy
Northwest Ohio