question

MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 asked MikhailFirsov-1277 answered

Question on @@ROWCOUNT

Hello,

Would you please help me with this?

89162-q.png



 UPDATE dbo.UsersCOMPUTERS2
 SET LastName = N'Test2'
 WHERE LastName = N'Test'
 IF @@ROWCOUNT = 0
 PRINT 'Warning! No rows were updated!!!'
 ELSE PRINT @@ROWCOUNT;
 go
sql-server-transact-sql
q.png (49.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

The documentation is not very clear on @@ROWCOUNT.

You should treat @@ROWCOUNT as a "read-once" variable. Anytime you use @@ROWCOUNT, it gets reset to 0. So your statement "IF @@ROWCOUNT" resets the value to 0. You need to store the value in a local variable if you want to reuse it.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SQLZealots avatar image
0 Votes"
SQLZealots answered

Try the below: The IF is resetting the value of @@rowcount in your case.

 Declare @RCnt bigint
 UPDATE dbo.T1
  SET Col1=3
  WHERE col1=2
  Set @RCnt = @@ROWCOUNT
  IF @RCnt = 0
  PRINT 'Warning! No rows were updated!!!'
  ELSE PRINT @RCnt;
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
1 Vote"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @MikhailFirsov-1277,

You need to assign the @@ROWCOUNT to a T-SQL variable first to preserve its value.
Please see a repro of it below.

SQL

 -- DDL and sample data population, start
 DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, city VARCHAR(30));
 INSERT INTO @tbl (city) VALUES
 ('Miami'),
 ('Orlando');
    
 DECLARE @counter INT;
 -- DDL and sample data population, end
    
 UPDATE @tbl
 SET city = 'Hollywood'
 WHERE ID = 1;
    
 SET @counter = @@ROWCOUNT;
    
 IF @counter = 0
  PRINT 'Warning! No rows were updated!!!';
 ELSE 
  PRINT @counter;
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 answered YitzhakKhabinsky-0887 edited

Thank you all for your replies!

"The IF is resetting the value of @@rowcount in your case. " - ??? If the "IF" was resetting the @@ROWCOUNT that query would always print "Warning! No rows were updated!!!'", but it prints it only when there was no rows indeed...

I was able to create a query with the variable, I just wanted to know whether I understand it correctly why the @@ROWCOUNT was 0 in the code above.

According to this page - https://docs.microsoft.com/en-us/sql/t-sql/functions/rowcount-transact-sql?view=sql-server-ver15 - the PRINT operator must set it to 0, SELECT - to 1 ( but I always kept getting 0 after SELECT - don't know why) and that was the cause of the "0".

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@MikhailFirsov-1277,

I just wanted to know whether I understand it correctly why the @@ROWCOUNT was 0 in the code above.

Because when T-SQL code is using/referring that system variable @@ROWCOUNT, its value gets reset to zero. In your particular case it was this line:

 IF @@ROWCOUNT = 0
0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

To add to the other posts: @@rowcount returns the number of rows affected by the most recently executed statement. When you come to the PRINT, the most recently executed statement is the IF.

For this reason, you should always capture @@rowcount in a local variable if you want to play with it.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @MikhailFirsov-1277,

The @@ROWCOUNT only returns the row count from the most recent statement.

It is highly volatile. So, basically, anything can reset it.

Typically, @@ROWCOUNT is used for error handling or for checking a business rule.

As mentioned by other experts, you could store @@rowcount in a local variable as below:

  SET @counter = @@ROWCOUNT;
        
  IF @counter = 0
   PRINT 'Warning! No rows were updated!!!';
  ELSE 
   PRINT @counter;

Or replace it with a message as below:

   IF @@ROWCOUNT = 0
  PRINT 'Warning! No rows were updated!!!'
  ELSE PRINT 'Rows were updated...';
  go

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 answered ErlandSommarskog commented

Thank you all for your explanations!

Yes, I know that using the variable for the value of the @@ROWCOUNT would solve the problem, but my question stems from the way the @ROWCOUNT is being reset - it seems rather strange to me:

89338-q2.png

Regards,
Michael



q2.png (28.7 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

but my question stems from the way the @ROWCOUNT is being reset - it seems rather strange to me:

It is not being reset - it's being set. You execute a statement. That sets @@rowcount to a new value.

And, yes, some things in SQL Server are strange.

And by the way, there is an error in your annotation. Below IF @@rowcount > 0, you have written that @@rowcount is a three-digit number. It is not. At that point it is a one-digit number. Which number is left as an exercise to the reader.

0 Votes 0 ·
MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 answered

Hello ErlandSommarskog,

Thank you for the clarification!

"Which number is left as an exercise to the reader" - yes, I should have written "360" under the previous row, under IF ... > 0 the value = 1.

Regards,
Michael

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.