SQL databases usually contain several hundred or even several thousand lines. But very often, modifications, selections, updates and other instructions only affect a few rows. To find out exactly how many rows have been affected by your various operations, you can use SQL ROWCOUNT. Deciphering.
SQL ROWCOUNT to count rows
@ROWCOUNT is an SQL query designed to return the number of rows affected by the last statement executed. This can be any SQL statement, such as INSERT, UPDATE, DELETE, SELECT…
For SQL ROWCOUNT to function correctly, it is imperative that both queries (ROWCOUNT and the statement affecting the rows) are in the same execution. If they are executed separately, the value returned will be 1, which does not correspond to the number of rows affected by the previously executed statement.
The aim of @@ROWCOUNT is to avoid infinite loops when a database is analyzed in a loop. When all rows have been processed, SQL ROWCOUNT automatically stops the analysis process.
In addition to processing data in a loop, this query can also be used to check the number of rows allocated in a statement. In this way, ROWCOUNT facilitates error handling.
💡Related articles:
5 examples of using SQL ROWCOUNT
SQL ROWCOUNT can be executed with any type of instruction. To help you understand, here are a few examples.
ROWCOUNT and SELECT
SELECT TOP (100)FROM [table_name] SELECT @@ROWCOUNT The number of rows affected by the SELECT query will then appear at the bottom of the table. It is also possible to call @@ROWCOUNT after executing two SELECT commands during the same operation. For example:
SELECT TOP (100)FROM [table_1_name] SELECT top 100 FROM [table_name_1] – [table_name_2] In this case, the SQL query ROWCOUNT only considers the second statement.
ROWCOUNT and UPDATE
By calling SQL ROWCOUNT after using a UPDATE query, you’ll be able to identify the number of rows updated during this operation.
Here’s how this combination looks:
UPDATE [table_name]
SET [column_name] = ‘new value’
WHERE condition
SELECT @@ROWCOUNT
Here again, the number of updated rows appears below the table.
ROWCOUNT and DELETE
The combination of ROWCOUNT and DELETE identifies the number of lines deleted.
The syntax is as follows:
DELETE [table_name]
WHERE condition
SELECT @@ROWCOUNT
ROWCOUNT and INSERT
With the INSERT query, the SQL command ROWCOUNT indicates the number of rows added.
Here’s the basic syntax:
INSERT INTO table
VALUES ('value 1', 'value 2', ...)
SELECT @@ROWCOUNTIt’s also possible to run batch queries by adding the GO command.
For example:
INSERT INTO table
VALUES (‘value 1’, ‘value 2’, ‘value 3’)
GO 10
SELECT @@ROWCOUNT
When there are several simultaneous queries in a single operation, SQL ROWCOUNT only takes the last query into account. In this example, even if the INSERT query is repeated 10 times, the ROWCOUNT command will only show 3 modifications, since this is the number of values modified by the last query.
This rule applies to all statements, whether INSERT. SELECT, UPDATE, DELETE, etc.
ROWCOUNT and IF
SELECT TOP (100)FROM [table_name] IF @@ROWCOUNT > 10 PRINT “message_1” ELSE PRINT “message_2”. In this example, this means: select the top 100 from [table_name]. If the number of rows is greater than 10, write message_1, otherwise, write message_2. In this case, as we’re selecting a top 100, the number of rows will indeed be greater than 10, so message_1 will appear.
Join DataScientest to master the SQL language
SQL ROWCOUNT is just one of the many possibilities offered by the SQL language. There are also updates, deletions, additions, conditions, triggers…
Mastering this language is a fundamental skill for any professional who needs to learn how to manage relational databases with fluidity. To do this, you need to combine theory (to know all the available queries) and practice (to apply them on a case-by-case basis). This is precisely what we offer at DataScientest with our numerous data-related training courses. Find out more!
