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: 
use `sp_executesql` and an `output parameter`

example

```
DECLARE @sqlBody VARCHAR(500),@TableCount INT, @SQL NVARCHAR(1000)

SELECT @sqlBody = 'from sysobjects'
SELECT @SQL = N'SELECT @TableCount = COUNT(*) ' + @sqlBody

EXEC sp_executesql @SQL, N'@TableCount INT OUTPUT', @TableCount OUTPUT

SELECT @TableCount
GO

```
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;

```