Subquery returned more than 1 value with Iif conditions

Question

select *
from employee
where controllingoffice in (
    Iif(1=1, (select id from controllingoffice), (select id from controllingoffice where OwnerId=4))
)

Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Note: Both subquery return more than 1 row

Answer 1

The reason your query fails is because your subqueries return multiple values while the iif() function is expecting scalar results for its arguments. Because iif() is a function, it evaluates to a single value and not a set of rows. You can't use it to branch as you would in a procedural language so you can't make the execution of the subqueries conditional in that sense.

The subquery below allows you to express the driving condition once in one place. That can be handy if this uses dynamic SQL. I'm presuming that your 1 = 1 is a placeholder for that input. The switching condition determines whether all rows are returned or just the ones for the single OwnerId:

select *
from employee
where controllingoffice in (
    select id from controllingoffice where
    <switch condition> or OwnerId = 4
);

By the way the logic X or (~X)Y reduces to X or Y

An approximation to what you intended to happen would look something like this:

select *
from employee
where
    1 = 1 and controllingoffice in (
        select id from controllingoffice
    )
 or 0 = 1 and controllingoffice in (
        select id from controllingoffice where OwnerId = 4
    );