Use if in where statement

Question

I have 4 table:

1. Table_op_type
2. Table_maintenancetype
3. Table_repair_time
4. Table_repair_type

Each table has one column that can true or false and I have one Table_maintenancereport that has many columns and 4 columns in Table_maintenancereport foreign key given from 4 up table when I select Table_maintenancereport and one row in each table is true code working fine but when more than one row returned from table give this error.

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

How can I fix it?

My code is:

SELECT *
FROM Table_maintenancereport
WHERE mtypeid IN (IIF(@smtype = 1, (SELECT Table_maintenancetype.id FROM Table_maintenancetype WHERE enable_search = 1), mtypeid))
  AND op_typeid IN (IIF(@sop IN (1), (SELECT Table_op_type.id FROM Table_op_type WHERE enable_search = 1), op_typeid))
  AND repaire_timeid IN (IIF(@stime IN (1), (SELECT Table_repair_time.id FROM Table_repair_time WHERE enable_search = 1), repaire_timeid))
  AND repaire_typeid IN (IIF(@stype = 1, (SELECT Table_repair_type.id FROM Table_repair_type WHERE enable_search = 1), repaire_typeid));

Answer 1

You need to fix the where conditions. You can do this with basic logic operators. Sets cannot be returned by iif() or by case() expressions.

So:

WHERE (@smtype <> 1 OR
       mtypeid IN (SELECT Table_maintenancetype.id FROM Table_maintenancetype WHERE enable_search = 1)
      ) AND
      (@sop <> 1 OR
       op_typeid IN (SELECT Table_op_type.id FROM Table_op_type WHERE enable_search = 1)
      ) AND
      (@stime <> 1 OR
       repaire_timeid IN (SELECT Table_repair_time.id FROM Table_repair_time WHERE enable_search = 1)
      ) AND
      (@stype <> 1 OR
       repaire_typeid IN (SELECT Table_repair_type.id FROM Table_repair_type WHERE enable_search = 1)
      );