I am doing some counts on a table (SQL 7) based on some criteria. The query is:

SELECT COUNT(*) AS NumRecs FROM [mytable] WHERE [some criteria]

I need to return the value lnCount as OUTPUT of a stored procedure. This can be accomplished by typing:

SELECT @NumRecs = COUNT(*) FROM [mytable] WHERE [some criteria]

and declare @NumRecs as an output parameter. However, [mytable] is a variable as well. I have managed to find two solutions, both of which seem to perform equally fast.

Solution 1
SET @lcParam = N'@NumRecs1 AS int OUTPUT'
SET @sqlcmd = 'SELECT @NumRecs1 = COUNT(*) FROM ' + @TableName + 'WHERE ...'
EXEC sp_executesql @sqlcmd, @lcParam, @NumRecs1 = @NumRecs OUTPUT

Solution 2
CREATE TABLE #tmp_count (cnt int)
SET @sqlcmd = 'INSERT #tmp_count SELECT COUNT(*)
FROM ' + @TableName + 'WHERE ...'
EXEC (@sqlcmd)
SELECT @NumRecs = cnt FROM #tmp_count

Is there a better way to do this? It seems to me that I am doing circles around the solution.

Thank you,