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
getNumTableRecords = rsCount![NumRecs]
Set rsCount = Nothing
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
Create PROCEDURE [dbo].[spMJORecCount]
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.