constructing dynamic In Statements with sql

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 th开发者_C百科e 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?

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.

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() + ");";

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

I think this should be read: Dynamic SQL.

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

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