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

```
You can use output variable in SP\_EXECUTESQL

```
DECLARE @SQL NVARCHAR(MAX);
DECLARE @ParamDefinition NVARCHAR(100) = '@ROW_SQL INT OUTPUT'
DECLARE @AFFECTED_ROWS INT;

SELECT
@SQL = N'SELECT 1 UNION ALL SELECT 2'
SELECT @SQL += 'SELECT @ROW_SQL = @@ROWCOUNT;';
EXEC SP_EXECUTESQL @SQL, @ParamDefinition, @ROW_SQL=@AFFECTED_ROWS OUTPUT;

PRINT 'Number of affected rows: ' + CAST(@AFFECTED_ROWS AS VARCHAR(20)); 

```

Ouput:

```
SQL2.sql: Number of affected rows: 2

```

Thanks Jesus Fernandez!