Use sp_executesql Or EXEC To Get The Count For Dynamic Table Names

You need the record count for a table but this table could change every day or change based on which user executes the stored procedure
There is no point in creating dozens of stored procedures, all this can be done by using exec (sql) or sp_executesql
Of course sp_executesql is much better and if you run the statements below you will also see that it's execution plan is only 29.04 percent relative to the batch
So let's get started with sp_executesql

USE pubs
GO

--sp_executesql
DECLARE @chvTableName VARCHAR(100),
@intTableCount INT,
@chvSQL NVARCHAR(100)

SELECT @chvTableName = 'Authors'
SELECT @chvSQL = N'SELECT @intTableCount = COUNT(*) FROM ' + @chvTableName

EXEC sp_executesql @chvSQL, N'@intTableCount INT OUTPUT', @intTableCount OUTPUT

SELECT @intTableCount
GO


--EXEC (SQL)
DECLARE @chvTableName VARCHAR(100),
@intTableCount INT,
@chvSQL NVARCHAR(100)


CREATE TABLE #temp (Totalcount INT)
SELECT @chvTableName = 'Authors'
SELECT @chvSQL = 'Insert into #temp Select Count(*) from ' + @chvTableName

EXEC( @chvSQL)

SELECT @intTableCount = Totalcount from #temp

SELECT @intTableCount

DROP TABLE #temp


First Hit CTRL + K (this will show the execution plan) , then highlight the complete code, hit F5 and look at the Execution Plan tab
As you can see sp_executesql is more than twice as efficient as exec (SQL)