为什么我不能获得@@ rowcount值？

Below is a simplified version of SQL script I have. print @RowNum always shows 0, rather than the real record number of the first result set. What's wrong? Thank you.

下面是我的SQL脚本的简化版本。 print @RowNum始终显示0，而不是第一个结果集的实际记录号。怎么了？谢谢。

declare @i int, @RowNum int
set @i=0
while @i<2
begin
    execute StoredProcedure @i --containing a big select
    if @i=0 set @RowNum=@@rowcount
    set @i=@i+1
end
print @RowNum

3 个解决方案

#1

because this if @i=0

因为这个如果@ i = 0

sets it to 0, even a print statement will set it to 0

将其设置为0，即使print语句也将其设置为0

now run this

现在运行这个

declare @i int, @RowNum int
set @i=0
while @i<2
begin
    if @i=0
    begin   
        execute StoredProcedure @i --containing a big select
        set @RowNum=@@rowcount
    end
    else
    execute StoredProcedure @i 
    set @i=@i+1
end
print @RowNum

here is another example

这是另一个例子

select 1
union all
select 2

select @@rowcount --2
go

now it will be 0

现在它将是0

select 1
union all
select 2
if 1=1
select @@rowcount --0

PRINT also messes it up, this will be 2

PRINT也搞砸了，这将是2

select 1
union all
select 2

select @@rowcount --2
go

this will be 0

这将是0

select 1
union all
select 2

print '1'
select @@rowcount -- 0

I created a post with more examples and explanations here: When should you store @@ROWCOUNT into a variable?

我在这里创建了一个包含更多示例和解释的帖子：什么时候应该将@@ ROWCOUNT存储到变量中？

#2

I'm gonna assume SQLMenace's answer is correct, but add, "Wouldn't this do what you want?":

我会假设SQLMenace的答案是正确的，但补充说，“这不会做你想要的吗？”：

    declare @RowNum int 
    execute StoredProcedure 0
    set @RowNum=@@rowcount 
    execute StoredProcedure 1 
    print @RowNum

#3

I would avoid this style in general. If you retrieve the number of rows selected from a table in the SP by querying @@rowcount after the call to the procedure, you are actually introducing an unnecessary dependency on how the procedure is implemented inside and compromise explicitness. If you later change the implementation of the procedure it may break the code outside and it would not be obvious while modifying the SP. You should instead use output parameter named appropriately.

我会避免这种风格。如果通过在调用过程之后查询@@ rowcount来检索从SP中的表中选择的行数，则实际上是对内部实现过程的方式引入了不必要的依赖性并损害了显式性。如果您稍后更改过程的实现，则可能会破坏外部代码，并且在修改SP时不会显而易见。您应该使用适当命名的输出参数。

#1