What is @@RowCount in SQL? What does this statement do @@RowCount?

The @@ROWCOUNT is a special variable of SQL. It will return the number of rows changed by the last statement.
The @@RowCount is equal to (=) the number of rows changed by the last statement.

Syntax: - @@ROWCOUNT

Return Types: - INT

What is the scope of @@RowCount?

The @@RowCount is both the scope and connection safe and it is read only!

For example as,

-- DECLARE RETURN TABLE
DECLARE @Return_Table TABLE (Code varchar(10)
 ,Message varchar(100), ID varchar(100))

--UPDATE CUSTOMER QUERY
UPDATE Customer 
 SET Name = N'Anil Singh' 
WHERE Id = 0786

--USE OF @@ROWCOUNT
IF (@@ROWCOUNT > 0)
BEGIN
 INSERT INTO @Return_Table (Code, Message, ID)
  SELECT 'OK', 'SUCCESS', SCOPE_IDENTITY()
 SELECT Code, Message, ID FROM @Return_Table
END
ELSE
BEGIN
 INSERT INTO @Return_Table (Code, Message, ID)
  SELECT 'ERROR', 'Warning - No rows updated.', SCOPE_IDENTITY()
 SELECT Code, Message, ID FROM @Return_Table
END
GO

Keep in Mind an Interesting point:-

@@ROWCOUNT is returns only integer value. Suppose that, you are working on bulk insert operations and you are updating millions of records in the database table that time @@ROWCOUNT is fail to returns the count values because it is an integer return types and your effected millions of rows in the table.

In that case, we are using ROWCOUNT_BIG() method to achieve this because it is bigint returns types.

Syntax: - ROWCOUNT_BIG()

Return Types: - BIGINT

For example as,

-- DECLARE RETURN TABLE
DECLARE @Return_Table TABLE (Code varchar(10)
 ,Message varchar(100), ID varchar(100))

--INSERT CUSTOMER ROWS
INSERT INTO ContactType(Code, Description, IsCurrent, CreatedBy, CreatedOn)
VALUES ('IT-PROGRAMING', 'This is a Prrogrammer!', 1, 'Anil Singh', GETDATE());

--USE OF ROWCOUNT_BIG() METHOD
IF (ROWCOUNT_BIG() > 0)
BEGIN
 INSERT INTO @Return_Table (Code, Message, ID)
 SELECT 'OK', 'SUCCESS', SCOPE_IDENTITY()
 SELECT Code, Message, ID FROM @Return_Table
END
ELSE
BEGIN
 INSERT INTO @Return_Table (Code, Message, ID)
 SELECT 'ERROR', 'Warning - No rows updated.', SCOPE_IDENTITY()
 SELECT Code, Message, ID FROM @Return_Table
END
GO

I hope you are enjoying with this post! Please share with you friends!! Thank you!!!

Pin it

Anil Singh is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..

My Tech Blog - https://www.code-sample.com/
My Books - Book 1 and Book 2