Check my faq#8130 How to return a value from dynamicaly executed T-SQL code. Here's a sample code based on the faq that uses pubs DB.

DECLARE @SQLstr nvarchar(100)
DECLARE @numrecs int, @TableName nvarchar(100)
SET @TableName = 'pubs..authors'
SET @SQLstr = N'SELECT @numrecs = Count(*) FROM ' + @TableName
EXEC sp_executesql @SQLSTR, N'@numrecs int OUTPUT',
				@numrecs OUTPUT
PRINT @numrecs

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