7

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.

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
phoenies
  • 537
  • 2
  • 5
  • 16

3 Answers3

14

because this if @i=0

sets it to 0, even a print statement will set it to 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

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

PRINT also messes it up, this will be 2

select 1
union all
select 2

select @@rowcount --2
go

this will be 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?

SQLMenace
  • 132,095
  • 25
  • 206
  • 225
  • anything after the result cause it to be reset, I also added an example with print, @@error has the same behavior – SQLMenace Aug 26 '10 at 14:10
  • Is it required behavior that ·if @i=0· affects @@rowcount value? You see I need the row number of the first result set. How can I achieve that? – phoenies Aug 26 '10 at 14:12
  • see modified code, if your else needs more than 1 statement then add BEGIN END around it – SQLMenace Aug 26 '10 at 14:16
0

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

    declare @RowNum int 
    execute StoredProcedure 0
    set @RowNum=@@rowcount 
    execute StoredProcedure 1 
    print @RowNum
James Curran
  • 101,701
  • 37
  • 181
  • 258
  • The example code is inaccurate. What after `execute` is actually a dynamically generated string, which is very complicated. I really don't prefer to repeat the generating part of code. – phoenies Aug 26 '10 at 14:17
0

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.

vaso
  • 213
  • 2
  • 9