@@ROWCOUNT Behavior With Aggregate Functions in SQL

I just wanted to share some analysis I did when I was working on a Stored Procedure created by another developer.

Sometimes you might find a different way to do error handling inside a Stored Procedure, like some people use the error code to check the behavior of the code or some other techniques. In this article I will discuss one of the techniques and its special behavior in a situation and why.

People use @@ROWCOUNT to check whether the data is coming in the last select operation or if I would say in a better term, if someone wanted to check whether data is present or not for further processing, then they used to check @@ROWCOUNT to verify whether any data is selected in the last select operation or not.

Let's see the following example, suppose you are selecting the salary of an employee based on his/her age from an employee table:

DECLARE @Salary NUMERIC (20,3)
SELECT @Salary=salary from tbl_EmployeeDetails
WHERE AGE<25

If (@@ROWCOUNT =0)
BEGIN
   RAISERROR ('Error', 0,1)
END

So you can see this is a very common approach that people use to handle errors. Now you might be thinking, what is special about it? Ok, so let's see the issue that you might not have noticed.

Suppose I wanted to get a MAX salary of an employee whose age is less than 25. So in this case you can write the logic as follows:

DECLARE @Salary NUMERIC (20,3)
SELECT @Salary=MAX (salary) from tbl_EmployeeDetails
WHERE AGE<25
If (@@ROWCOUNT =0)
BEGIN
   RAISERROR ('Error', 0,1)
END

So now again, what's special here, it's quite straight forward. Are you able to find the problem in the code above?

Yes, then super, you can stop reading the article, if not then let's see what the issue is. When you run this code, it will work fine until and unless there is no employee whose age is less than 25 so suppose in this company, there is no employee, whose age is less than 25. Now it will return 1 even if there are no employee's data from the select operation and it will not raise an error and the execution will continue further that could be the cause of another exception like divide by zero if you are using this salary in some calculation and so on.

Now the question that should pop up in your mind is, what is the reason for that? In a simple way there is no difference, but when you try to see the execution plan, you can figure it out.

First, let's understand the definition of @ROWCOUNT, it returns how many rows are affected/processed due to the last operation.

As you can see in the query plan, for the selective operation there is no data to select. Since the number of rows affected/processed is zero, so the @@ROWCOUNT value would be zero. But if you see the query plan with the aggregate function that I used here as MAX, there are two operations; the first is the same as before, there is no data for selection, but the second one is the MAX function that operates on a single row that has NULL. So when we check the @ROWCOUNT then you will be get 1 due to the aggregate function because the last operation that is calculating MAX, is done on a single row (NULL).

Now let's see the query execution plan for getting a more detailed view.

 

1. The following screen shot shows the actual execution plan for the select operation and as you can see the actual number of rows is 0 so @@ROWCOUNT is zero because there was no row processed in the last operation.
   
   
   
   
2. The following screen shot shows the execution plan of a select operation on the table before the aggregate function executes.
   
   
   
   
3. The following screen shot shows that 1 row was processed so @@ROWCOUNT would be 1.
   
   

So the next question is, how to resolve it? There are many ways I can think of to check whether or not the obtained data is NULL, so you can put a condition like the following:

IF( @Salary IS NULL)
    BEGIN
     RAISE_ERROR()
   END