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: 
You could instead have the dynamic query return the result as a row set, which you would then insert into a table variable (could be a temporary or ordinary table as well) using the `INSERT ... EXEC` syntax. Afterwards you can just read the saved value into a variable using `SELECT @var = ...`:

```
DECLARE @rowcount TABLE (Value int);
INSERT INTO @rowcount
EXEC('SELECT COUNT(*) ' + @sqlBody);
SELECT @ActualNumberOfResults = Value FROM @rowcount;

```
Late in the day, but I found [this method](http://www.sqlservercentral.com/Forums/FindPost487103.aspx) much simpler:

```
-- test setup
DECLARE @sqlBody nvarchar(max) = N'SELECT MyField FROM dbo.MyTable WHERE MyOtherField = ''x''';
DECLARE @ActualNumberOfResults int;

-- the goods
EXEC sp_executesql @sqlBody;
SET @ActualNumberOfResults = @@ROWCOUNT;
SELECT @ActualNumberOfResults;

```