I am writing a (very simple) stored procedure that counts records on a file name. The stored proc is as follows:
------------------------------------------
CREATE PROCEDURE GetRecordCount
@TableName varchar(30),
@NumRecs int OUTPUT
AS

DECLARE @sqlcmd varchar(250)
SET @sqlcmd = 'SELECT @NumRecs = COUNT(email)
FROM ' + @TableName
WHERE Include = 'Y'
EXEC (@sqlcmd)
------------------------------------------
The intention is to return variable @NumRecs. I am calling the routine as follows:

DECLARE @MyNumber int
EXEC GetRecordCount "NASA", @NumRecs = @MyNumber OUTPUT
PRINT @MyNumber

When I execute the above line, I get an error message:
Server: Msg 137, Level 15, State 1, Line 0
Must declare the variable '@NumRecs'.


If I replace the "SET @sqlcmd = .." AND "EXEC(.." with:
SELECT SELECT @NumRecs = COUNT(email)
FROM MyTable (i.e. without passing the table name), the process works fine. How can I construct an sql command using a parameter?