constructing dynamic In Statements with sql

Question

Suppose we need to check three boolean conditions to perform a select query. Let the three flags be 'A', 'B' and 'C'.

If all of the three flags are set to '1' then the query to be generated is SELECT * FROM Food WHERE Name In ('Apple, 'Biscuit', 'Chocolate'); If only the flags 'A' and 'B' are set to '1' with C set to '0'. Then the following query is generated.

SELECT * 
  FROM Food 
 WHERE Name In ('Apple, 'Biscuit');

What is the best way to do it?

Answer 1

First may be you need to check if all are false and show error. Or may be not if it is acceptable in your case.

Then if these flags are mere bool variables do (pseudocode)

sql = "SELECT * 
  FROM Food 
 WHERE Name In (";
if (A) sql += "'Apple', "
if (B) sql += "'Biscuit', "
if (C) sql += "'Chocolate', "
sql = sql.deleteLastCharacter() + ");";

Answer 2

Why don't you include A/B/C in the query? select * from food where (name = 'Apple' or NOT A) and (name = 'Biscuit' OR NOT B)...

Answer 3

I think this should be read: Dynamic SQL.

The sp_executesql system stored procedure reveals to be pretty useful also.

Answer 4

SELECT * 
  FROM Food
 WHERE (Name = 'Apple' AND <condition A>)
    OR (Name = 'Biscuit' AND <condition B>)
    OR (Name = 'Chocolate' AND <condition C>)

Now, while being correct this is not desirable from performance point of view since conditions A, B, and C are not data driven (they don not change from row to row). So you can use permutations of all possible conditions by constructing SQL dynamically - use IN clause and construct its string dynamically.

Yet another solution is assembling final result in the client by running each SELECT separately (pseudo-code):

if A then {
   result1 = execute("SELECT * FROM Food WHERE Name = 'Apple')
}
if B then {
   result2 = execute("SELECT * FROM Food WHERE Name = 'Biscuit')
}
if C then {
   result2 = execute("SELECT * FROM Food WHERE Name = 'Chocolate')
}

result = join(result1, result2, result3)

This solution may work when you have high percentage of cases with just one or two true conditions.

Answer 5

This is a really complex topic that has many subtle performance implications. You really need to read these excellent articles by Erland Sommarskog:

Dynamic Search Conditions in T-SQL

The Curse and Blessings of Dynamic SQL