SQL SELECT statement within an IF statement

Question

I have a trigger in SQL Server that needs to check on an update the number of rows with a value between a certain amount and do something accordingly. My current code is something like this:

IF EXISTS(SELECT COUNT(id) as NumberOfRows
          FROM database
          WHERE id = 3 AND value <= 20 and value > 2
          GROUP BY id
          HAVING COUNT(id) > 18)
    -- if true, do something

From what I can tell, the select statement should find the number of rows with a value between 2 and 20 and if there are more than 18 rows, the EXISTS function should return 1 and the query will execute the code within the IF statement.

However, what is happening is that it is always executing the code within the IF statement regardless of the number of rows with a value between 2 and 20.

Any ideas on why this might be? I can post more complete code if it might help.

possible duplicate of [SQL using IF ELSE statement based on count to execute different insert statements](http://stackoverflow.com/questions/14072140/sql-using-if-else-statement-based-on-count-to-execute-different-insert-statement) — VMAtm, Mar 12 '15 at 23:52

score 1 · Accepted Answer · edited May 23 '17 at 11:50

1

The reason is that the Exists function is checking the result of the sub-query for existing - are there any rows or not. And, as you return the COUNT, it'll never be not-existing - COUNT returns 0 if there are no rows presented in database.

Try to store the resulting count in a local variable, like in this question:

Using IF ELSE statement based on Count to execute different Insert statements

DECLARE @retVal int

SELECT @retVal = COUNT(*) 
FROM TABLE
WHERE COLUMN = 'Some Value'

IF (@retVal > 0)
BEGIN
    --INSERT SOMETHING
END
ELSE
BEGIN
    --INSERT SOMETHING ELSE
END

edited May 23 '17 at 11:50

Community

1
1

answered Mar 12 '15 at 23:48

VMAtm

27,943
17
79
125

This is wrong. EXISTS looks for the presence of **rows**, it doesn't matter if they only consist of NULLs or not. The suggested solution will work but the problem is not NULLs or absence of NULLs. – Andriy M Mar 13 '15 at 07:42
Sorry but still wrong. COUNT is just a scalar value. The issue is not the value returned by COUNT. In fact, EXISTS ignores *values*, what matters is *rows*. The subquery has a HAVING clause. That means that the output *can* be empty. (It will be empty if no group matches the HAVING condition.) As I said in my answer, the problem is elsewhere. – Andriy M Mar 13 '15 at 08:00

Ziarek · Answer 2 · 2020-03-20T16:09:46.603

1

I would do it like so (single line):

IF ((SELECT COUNT(id) FROM table WHERE ....)>18) BEGIN
...do something

You can even do between in a single line

IF ((SELECT COUNT(id) FROM table WHERE ....)between 2 and 20) BEGIN
...do something
END

edited Mar 20 '20 at 16:09

answered Mar 20 '20 at 16:03

Ziarek

679
6
14

score 0 · Answer 3 · answered Mar 13 '15 at 07:51

Your subquery is looking for matches in the entire table. It does not limit the results only to those that are related to the rows affected by the update. Therefore, if the table already has rows matching your condition, the condition will be true on any update that affects other rows.

In order to count only the relevant rows, you should either join the database table to the inserted pseudo-table or use just the inserted table (there is not enough information in your question to be sure which is better).

SQL SELECT statement within an IF statement

3 Answers3