Tsql Continue Loop Until Rowcounter

In this article, we will discuss the SQL Server @@ROWCOUNT and ROWCOUNT system objects by going through practical examples that cover most of these system objects usage.

Usage

SQL Server @@ROWCOUNT is a system variable that is used to return the number of rows that are affected by the last executed statement in the batch.

The rows affecting statement can be any INSERT, UPDATE, DELETE or SELECT statement that is executed directly before the @@ROWCOUNT execution, taking into consideration that both the rows affecting statement and the system variable calling query are in the same execution.

@@ROWCOUNT is used frequently in the loops to prevent the infinite loops and stop the current process when all the target rows are processed. It is also used for error handling to check the number of affected rows within the statement.

Examples

Let us go through several examples that show the different ROWCOUNT usage scenarios.

Number of selected rows

The simple use for the SQL Server @@ROWCOUNT system variable is calling it directly after a SELECT statement. In the below T-SQL query, a SELECT query is used to retrieve the top 100 records from the Employee table, and in the same execution, we retrieve the number of rows that are read from the source table, which is 100 rows as in the example, as shown below:

Example 1

If we try to modify the previous query to retrieve the top 1000 records from the target table, then try to check the value stored in the SQL Server @@ROWCOUNT system variable, you will see that the returned value is 290, not 1000, as the previous SELECT statement read the whole source table's rows, which is 290 only, as shown below:

Example 2

If you run two SELECT statements in the same execution, followed by calling @@ROWCOUNT, you will see that it will return the number of rows returned from the second SELECT statement. In the below script. The first SELECT statement returned 290 rows and the second SELECT statement returned 296 rows. The SQL Server @@ROWCOUNT will return 296, which is the number of rows affected by the second SELECT statement, as shown below:

Example 3

Number of updated rows

The SQL Server @@ROWCOUNT system variable can be used also to check the number of rows that are affected by an UPDATE statement. In the following script, the value of the @@ROWCOUNT system variable, which reflects the number of rows updated by that statement, is retrieved in the same execution of the UPDATE statement, as shown below:

Example 4

Number of deleted rows

You can also use the SQL Server @@ROWCOUNT system variable to return the number of rows that are affected by the executed DELETE statement. In the script below, the value of the @@ROWCOUNT system variable, which reflects the number of rows deleted by the executed statement, is retrieved in the same execution of the DELETE statement, as follows:

Example 5

Batch execution

In the batch executions, the SQL Server @@ROWCOUNT system variable will store the number of rows affected by the last batch execution. For example, if we try to execute an INSERT statement 20 times, using the GO 20 statement, then check the value stored in the @@ROWCOUNT system variable, you will find that the value equal to 1. This is because each INSERT statement in this query affects only one row. So, the last INSERT statement will insert one row then store that rows number in the system variable, as shown below:

Example 6

What will happen if I call it in a separate execution?

An important thing to consider here is that, if you try to run a specific query then call the SQL Server @@ROWCOUNT in a different execution, the value returned will not reflect the correct number of rows affected by the previous query, and will return 1, which is the number of rows affected by the @@ROWCOUNT calling statement, as shown below:

Example 7

To control the flow

The SQL Server @@ROWCOUNT can be used in the IF statement condition to control the flow of the statement, based on the number of rows returned from the last statement. For example, a SELECT statement is used to retrieve a number of rows from the source table, then an IF condition statement is used to check the number of rows retrieved from that table and print a relative comment, as shown below:

Example 8

Control a loop

The SQL Server @@ROWCOUNT system variable can be used also to control the loop and stop it when the number of rows matches the specified condition reach a specific value, rescuing it from falling in an infinite loop. In the script below, a WHILE loop will continue executing the entire statement, that is used to delete all rows with NULL GUIDs in batches, and stop executing when the number of rows that match that condition equal to 0, as shown below:

Example 9

Why it returns zero?

In SQL Server, many statements will reset the value stored in @@ROWCOUNT to 0. These statements include DEALLOCATE CURSOR, CLOSE CURSOR, PRINT, RAISERROR, BEGIN TRANSACTION, COMMIT TRANSACTION, BEGIN TRY/CATCH or END TRY/CATCH.

In the below script, we execute a SELECT statement within a TRY block but this statement will fail due to divide by zero error and another SELECT statement within the CATCH block will be executed instead. If we try to check the number of rows affected by the SELECT query to identify which SELECT query is executed, we will see that the result returned from this variable is 0 as it is reset by the END CATCH block, as shown below:

Example 10

To overcome this issue, we need to store the number of affected rows into another variable immediately after executing each statement, in order to use that value later.

In the following script, we defined a variable and store the number of rows in that variable after the SELECT statements executed inside both the TRY and CATCH blocks, then return that user variable, where it will reflect the number of rows affected by the SELECT query inside the CATCH block, as shown below:

Example 11

Number of Rows > 2 billion!

The SQL Server @@ROWCOUNT system variable will not work if the number of rows affected by the query exceeds the 2 billion rows. This is because the data type of that system variable is INT.

To overcome this issue, SQL Server introduces the ROWCOUNT_BIG system function, which returns the number of rows affected by a specific query in the BIGINT data type.

It can be called directly after the statement you are interested in checking it, as shown clearly below:

Example 12

Control the Number of rows

SET ROWCOUNT is a system object that enforces the SQL Server Engine to stop processing the query after the specified number of rows are returned, which is somehow similar to the TOP clause!

Microsoft does not recommend using SET ROWCOUNT with DELETE, INSERT, and UPDATE statements and uses the TOP syntax instead, as it will not affect these statements in a future release of SQL Server.

In the below script, the SET ROWCOUNT is used to override the TOP keyword in the SELECT statement and limit the number of rows to a smaller value, where the SELECT query will return only 10 records, as shown below:

Example 13

You can turn the ROWCOUNT off, and allow the statement to return all rows, by setting its value to 0. For example, the SELECT statement below will return the 200 rows, without being limited by the specific number of rows, as shown below:

Example 14

SET ROWCOUNT can be also used to limit the number of deleted rows, which is not recommended. In the script below, we can see that the number of rows that match the specified condition is 30 rows and we use the SET ROWCOUNT to delete only 5 rows, as shown below:

Example 15

As the SET ROWCOUNT will be deprecated for the INSERT, UPDATE and DELETE statements, we can easily replace it with the TOP syntax and delete only 5 rows, as shown below:

Example 16

Conclusion

We explored the SQL Server @@ROWCOUNT and ROWCOUNT system objects by going through several examples in this article. This system variable returns the number of rows affected by the last executed statement in the batch and is extremely beneficial in loops and error handling.

Author
Recent Posts

Ahmad Yaseen is a Microsoft Big Data engineer with deep knowledge and experience in SQL BI, SQL Server Database Administration and Development fields.

He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.

Also, he is contributing with his SQL tips in many blogs.

View all posts by Ahmad Yaseen

matasaillut43.blogspot.com

Source: https://www.sqlshack.com/working-with-sql-server-rowcount/