This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
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.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(96, 26%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EYK%3C/text%3E%3C/svg%3E)
Hi @Mikhail Firsov ,
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;
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;
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://learn.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.