>>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'
That certainly works Sergey. However, the PRINT @MyNumber line does not print a value. That means that the value (@NumRecs) was not returned to the calling program properly.
Aristotle