Count rows in result of SQL query

To receive quantity of rows of the returned records of SQL query, you should use function @@ROWCOUNT. Function @@ROWCOUNT returns the quantity of rows, which were used in a query.

SELECT Id, Name, Position from Users

SELECT @@ROWCOUNT

SELECT Id, Name, Position from Users

SELECT @@ROWCOUNT

Result is: 188

In this case it means that table "Users" has 188 records because it returns all rows.

One of practice tutorials for the function @@ROWCOUNT is to count how many rows where updated after SQL update query. You can see an example of usage @@ROWCAOUNT after update:

UPDATE Users SET IsBoss = 1 where Level > 3 
declare @S int = @@ROWCOUNT 
IF @@ROWCOUNT = 0 
        BEGIN 
                SELECT 'Nothing updated' 
        END 
        
ELSE 
        BEGIN 
                SELECT @S 
        END

UPDATE Users SET IsBoss = 1 where Level > 3

declare @S int = @@ROWCOUNT

IF @@ROWCOUNT = 0

BEGIN

SELECT 'Nothing updated'

END

ELSE

BEGIN

SELECT @S

END

The value of @@ROWCOUNT has been changed after each query. That’s why in this example you can see the variable @S which value is equal to @@ROWCOUNT after UPDATE query.

6 April 2016, admin
SQL
Tags: MSSQL, sql, SQL query, Transact-SQL
Comments: (0)

Count rows in result of SQL query

No Comments »

Leave a comment

New posts