Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Count records returned by stored procedure to store in DashBoard table

Posted on 2014-09-23
4
Medium Priority
?
666 Views
Last Modified: 2014-09-28
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.
0
Comment
Question by:Dale Fye
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 2000 total points
ID: 40340350
Place something similar in each stored-procedure where you want to capture audit info into tbl_Dashboard:
/*
**	declare local variables to capture system info
*/
DECLARE	@li_Error	int
,	@li_RowCount	int

/*
**	some insert/update/delete statement here
*/

-- capture system variables affected by above into local variables
SELECT	@li_Error = @@error, @li_RowCount = @@rowcount
IF @li_Error <> 0 BEGIN
-- do some error handling
END

/*
**	Populate tbl_Dashboard here using @li_RowCount
*/

Open in new window

0
 
LVL 48

Author Comment

by:Dale Fye
ID: 40340407
John,

All of these particular SPs are select queries  so, could I simply call another SP and pass it the name of the SP and the @@RowCount value?  Something like:
SELECT * FROM table1

exec df_Dashboard_Update 'Stored procedure #1',  @@RowCount

Open in new window

or will the exec line affect the records returned to Access when it calls the SP?
0
 
LVL 11

Assisted Solution

by:John_Vidmar
John_Vidmar earned 2000 total points
ID: 40342878
System variables get reset by almost any statement, if your example were to work then you are relying on no other developer from adding a statement between your select-statement (which populates @@rowcount) and the following stored-procedure call (which uses the @@rowcount).  Safer to capture @@rowcount into a variable and pass that to df_Dashboard_Update.

Due to scoping issues, I highly doubt that calling a stored-procedure (which contains only a select-statement) would transfer the @@rowcount to the next stored-procedure, i.e., the following would NOT work:
exec 'StoredProcedure1'

exec df_Dashboard_Update 'StoredProcedure1',  @@RowCount

Open in new window

0
 
LVL 48

Author Closing Comment

by:Dale Fye
ID: 40348782
Thanks for the input John.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question