Hello,
Would you please help me with this?

UPDATE dbo.UsersCOMPUTERS2
SET LastName = N'Test2'
WHERE LastName = N'Test'
IF @@ROWCOUNT = 0
PRINT 'Warning! No rows were updated!!!'
ELSE PRINT @@ROWCOUNT;
go
Hello,
Would you please help me with this?

UPDATE dbo.UsersCOMPUTERS2
SET LastName = N'Test2'
WHERE LastName = N'Test'
IF @@ROWCOUNT = 0
PRINT 'Warning! No rows were updated!!!'
ELSE PRINT @@ROWCOUNT;
go
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.
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;
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;
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".
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
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.
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.
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:

Regards,
Michael
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.
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
11 people are following this question.