How to get row count from EXEC() in a TSQL

问题:

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?

回答1:

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;

回答2:

Late in the day, but I found this method 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;

回答3:

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

回答4:

After executing your actual query store the result of @@ROWCOUNT in any variable which you can use later.

EXEC sp_executesql 'SELECT TOP 10 FROM ABX'

SET @TotRecord = @@ROWCOUNT into your variable for later use.

回答5:

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;

回答6:

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.