Question: I have a TSQL sproc that builds a query as and executes it as follows:

```
EXEC (@sqlTop + @sqlBody + @sqlBottom)

```

@sqlTop contains something like SELECT TOP(x) col1, col2, col3... 

TOP(x) will limit the rows returned, so later I want to know what the actual number of rows in the table is that match the query.

I then replace @sqlTop with something like:

```
EXEC ('SELECT @ActualNumberOfResults = COUNT(*) ' + @sqlBody)

```

I can see why this is not working, and why a value not declared error occurs, but I think it adequately describes what I'm trying to accomplish.

Any ideas?

Answer: 
Keep in mind that dynamic SQL has its own scope. Any variable declared/modified there will go out of scope after your `EXEC` or your `sp_executesql`.

Suggest writing to a temp table, which will be in scope to your dynamic SQL statement, and outside.

Perhaps put it in your `sqlBottom`:

```
CREATE TABLE ##tempCounter(MyNum int);

EXEC('SELECT @ActualNumberOfResults = COUNT(*) ' + @sqlBody + 
       '; INSERT INTO ##tempCounter(MyNum) VALUES(@ActualNumberOfResults);');

SELECT MyNum FROM ##tempCounter;

```
The only problem with the answers that create temporary tables (either using "DECLARE @rowcount TABLE" or "CREATE TABLE ##tempCounter(MyNum int)") is that you're having to read all the affected records off disk into memory. If you're expecting a large number of records this may take some time.

So if the answer is likely to be large the "use sp\_executesql and an output parameter" solution is a more efficient answer. And it does appear to work.