Stored Proc to Return Reccount for SQL Statement

I am new to SQL Server.  From EE contributions I now have an SP that will return the number of records in a Table:  Here it is:

GO
/****** Object:  StoredProcedure [dbo].[spMJORecCount]    Script Date: 9/16/2015 10:17:59 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 ALTER PROCEDURE [dbo].[spMJORecCount] (@TableName varchar(75))
 AS

BEGIN

       DECLARE @SQL varchar(250);
         SELECT @SQL = 'SELECT Count(*) from ' + @TableName;
         Exec(@SQL);
       
 END; 

Open in new window


For large volume transactions I use returned record count number to show the user a status of the processing, "Processing 4,123 of 12,345"

In many cases I don't want to know how many records are in the entire table but how many are in a SQL statement or perhaps even a query ,

For example I have a MS Access 2013 VBA processing loop driven by this sql statement:

"Select * from " & inputTable & " Where [Year] = 2015"

I would like a stored procedure that would return the count of records in the evaluated Select statement.

Some processing loops are drive by predefined queries such a "qrySelectActiveRecords"

I would also like a stored procedure to return the count of records in the evaluated query.

Best of all would be one stored procedure to handle all three situations.
LVL 1
mlcktmguyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
Not sure it is a good idea. That mean that you would have to pass the query to your SP, to replace the SELECT clause with a SELECT COUNT(*), to run it, before being able to return your number or rows.

In your code, you probably have your 12,345 rows loaded into a datatable no? You could use the Count property on this datatable.
mlcktmguyAuthor Commented:
I am new to SQL so I am definitely not up to speed on best/most efficient practices.  Any advice or input would be very welcome.

I am converting an existing MS Access 2013 DB to SQL because of the Access size limitations.

To answer your question:  All the data is in the SQL DB table that contains over 500,000 records.  That is the only place the 12,345 records I want will be.

The first SP to get the number of records in a table was suggested on EE.  Is there a better way to accomplish what I want to do?
ste5anSenior DeveloperCommented:
Using generic stored procedure is always a bad idea..

Please rephrase your question and give us more context information.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Éric MoreauSenior .Net ConsultantCommented:
How do you get your 12,345 rows that you have to process? You are probably looping through them.
mlcktmguyAuthor Commented:
I'm reading the table from MS Access using an ADO connection.  I create the SQL statement to open the table.
'
selectString = "Select * from " & inputTable & " Where [TaxYear] = 2015"
'
Dim rsIn2 As ADODB.Recordset
Set rsIn2 = New ADODB.Recordset
rsIn2.Open selectString, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
'
If rsIn2.EOF Then
    '
Else
 '   If rsIn2.RecordCount > 0 Then
        '
        totRecs = getRecordCount_SQL_SP(inputTable)
        displayMax = 500
        displayCount = displayMax + 1
        '

Open in new window


The statement " totRecs = getRecordCount_SQL_SP(inputTable)" calls the stored procedure and gets the record count of the entire input table.  I was hoping there is an efficient way of getting just the number of records that would be returned as a result of the SQL statement.
Éric MoreauSenior .Net ConsultantCommented:
You already have that info in rsIn2.RecordCount
mlcktmguyAuthor Commented:
I most definitely use the recordcount property when working with Access tables.

However, the record count property is not set (always equal -1) when connecting to a SQL table.  That's the reason for the question.
Éric MoreauSenior .Net ConsultantCommented:
as explained in https://support.microsoft.com/en-us/kb/194973, change your cursor location to adUseClient.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlcktmguyAuthor Commented:
Thanks you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.