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 — VMAtm
– VMAtm, Commented Mar 12, 2015 at 23:52

Ziarek · Accepted Answer · 2020-03-20 16:09:46Z

2

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, 2020 at 16:09

answered Mar 20, 2020 at 16:03

Ziarek

9177 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:50:53Z

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, 2017 at 11:50

CommunityBot

11 silver badge

answered Mar 12, 2015 at 23:48

VMAtm

28.4k17 gold badges85 silver badges133 bronze badges

2 Comments

Andriy M Over a year ago

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 Over a year ago

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 · Accepted Answer · 2015-03-13 07:51:34Z

0

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).

answered Mar 13, 2015 at 7:51

Andriy M

78k18 gold badges100 silver badges157 bronze badges

Collectives™ on Stack Overflow

SQL SELECT statement within an IF statement

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related