Mike,
I have a similar situation (unfortunately) where the table name is a variable. The simplified code here is:

SET @count = (SELECT COUNT(*) FROM @myTable)

A way to get this to work is to write:

SET @SQLcmd = 'SET @NumRecs = (SELECT COUNT(*) FROM @myTable)'
EXEC(@SQLcmd)

But, of course, @NumRecs is not available to the rest of the stored procedure. I have found two solutions:

1. Store data to a temp table:

CREATE TABLE #tmp_count (cnt int)
SET @SQLcmd = 'INSERT INTO #tmp_count SELECT COUNT(*) FROM @myTable'
EXEC(@SQLcmd)
SELECT @NumRecs = cnt FROM #tmp_count

2. A bit more complex, but more flexible:

SET @lcParam = N'@lcResult AS nvarchar(4000) OUTPUT'
SET @lcSQLcmd = '
  DECLARE @lnCount AS int
  SELECT @lnCount = (SELECT COUNT(*) FROM @myTable)
  SET @lcResult = CAST(@lnCount AS varchar)'

EXEC sp_executesql @lcSQLcmd, @lcParam, @lcResultString = @lcResult OUTPUT
SELECT @lcResult

Is there another, simpler way to "pass" the results to a variable, or this is pretty much it?

Thank you,