This won't work. When you use dynamic SQL, SQL Server considers it a seperate batch. It's not optimized when the compiler hits it and it has no access to variables outside of it
If you want to make it work, you'll have to write the results to a temporary table and then read it from there.
CREATE PROCEDURE GetRecordCount
@Tablename varchar(30),
@NumRecs integer OUTPUT
AS
CREATE TABLE #cnt (cnt integer)
EXECUTE (INSERT INTO #cnt VALUES (SELECT COUNT(*) FROM '+@TableName+' WHERE include = ''Y'' ')
SELECT @NumRecs = cnt FROM #cnt
RETURN 0
Or something like that <s>
-Mike
