Stored Procedure To Return Number Of Recs In A table

I am very new to SQL server although I have been programing for decades.  I am converting an existing application with an Access backend DB to SQL Server.

I have a function in my VBA code that returns the number or records in a table.  

Public Function getNumTableRecords(passedTable As String) As Long
'
getNumTableRecords = 0
selectString = " Select Count(*) as NumRecs from " & passedTable
'
Dim rsCount As ADODB.Recordset
Set rsCount = New ADODB.Recordset
rsCount.Open selectString, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
'
If rsCount.EOF Then
    '
Else
     '
    getNumTableRecords = rsCount![NumRecs]
End If
'
rsCount.Close
Set rsCount = Nothing

End Function

Open in new window


It works just fine, even with the linked SQL tables but for practice and perhaps more efficiency I would like to replace it with a simple stored procedure.  It’s only the second sp I’ve attempted and the first that passes a parameter.  What I have so far is

USE [JTSConversion]
GO
Create PROCEDURE [dbo].[spMJORecCount] 

	@TableName varchar(75)
AS

BEGIN

	SELECT Count(*) 
	from @TableName 
	
END

Open in new window


I get Msg 1087, Must declare the table variable “@TableName”.  I thought I had declared it and don’t know where to go from here.  Any help would be appreciated.  JTSConversion is the name of the database.

Perhaps there is even a better more efficient way of getting the number of records in a table.
LVL 1
mlcktmguyAsked:
Who is Participating?
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.

hieloCommented:
Try:
USE [JTSConversion]
GO
Create PROCEDURE [dbo].[spMJORecCount] 

	@TableName varchar(75)
AS

BEGIN
DECLARE @SelectString NVARCHAR(500)

-- Build your query by concatenating the string together
SET @SelectString = 'SELECT Count(*) from ' + @TableName 
	
END

Open in new window


http://www.codeproject.com/Articles/20815/Building-Dynamic-SQL-In-a-Stored-Procedure (scroll to Dynamic SQL in a stored procedure).
https://msdn.microsoft.com/en-us/library/ms188001.aspx
0
dameyCommented:
Your missing parenthesis ..
Create PROCEDURE [dbo].[spMJORecCount] (

      @TableName varchar(75)
)
AS ....

However this won't work because you can't use a table name from a string.

You would have to write it like this...

USE [JTSConversion]
GO
Create PROCEDURE [dbo].[spMJORecCount] (@TableName varchar(75))
AS

BEGIN

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

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:
Thank you
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.