如何从 TSQL SPROC 中的 EXEC() 获取行数? [英] How to get row count from EXEC() in a TSQL SPROC?

问题描述

我有一个 TSQL sproc，它构建一个查询并按如下方式执行它:

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

EXEC (@sqlTop + @sqlBody + @sqlBottom)

@sqlTop 包含类似 SELECT TOP(x) col1, col2, col3...

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

TOP(x) 会限制返回的行数，所以稍后我想知道表中与查询匹配的实际行数是多少.

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.

然后我用类似的东西替换@sqlTop:

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.

有什么想法吗?

推荐答案

您可以改为让动态查询将结果作为行集返回，然后将其插入到表变量中(可以是临时表或普通表作为好吧)使用 INSERT ... EXEC 语法.之后，您可以使用 SELECT @var = ... 将保存的值读入变量:

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;

这篇关于如何从 TSQL SPROC 中的 EXEC() 获取行数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！