I have a series of processes (Step #1) I'm running each morning to push/pull data into SQL Server from a variety of different data sources. Thanks to numerous responses here, those processes are working properly and get run normally at about 05:00 each morning. I'm not using SSIS at the moment because of the amount of data manipulation I'm having to do to get it into the structure I need.
Some of the data from these sources is suspect (imagine that), where values don't match from the multiple sources or even from within different tables a single data source. So I have a number of other stored procedures which I am using to identify values in records in these tables which need to be scrutinized and resolved. Those stored procedures, and the Access forms I'm using to view them are working great.
What I want to do is immediately after these processes (step #1 above) are run, I want to run through all of these other SPs, count the number of records returned by each of those stored procedures, and update the RecCount field (int) in tbl_Dashboard. This way, when the users come in, they can open the application to the Dashboard and immediately know where they need to focus their attention (without having to run the SPs at that time).
How would you go about this? Could I put another field in the Dashboard table with the name of the stored procedure and simply run a cursor to loop through that table, building a dynamic SQL string that returns the number of records and updates the [RecCount] field in the same table?
One caveat, these SPs all have two parameters (@StartDate and @EndDate). I would pass these parameter to this SP when it gets called.
I know how I would do this from Access, but since so much of the process is running in SQL Server, I would like to know how you experts might approach this.