>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?

---

Try this

SET @sqlcmd = 'SELECT [@NumRecs] = COUNT(email)
FROM ' + @TableName
WHERE Include = 'Y'