Good day everyone I have some problem in regards with using dynamic query with too many variables. To explain further below is some part of my code.

String QueryString = '';
List<List<String>> whereInList = new List<List<String>>();
WhereInList.add(new List<String>{'FirstName','0'});
WhereInList.add(new List<String>{'LastName','1'});
WhereInList.add(new List<String>{'Email','2'});

Map<String, Set<Object>> mapWhereField = new Map<String, Set<Object>>();
mapWhereField.put('FirstName',new Set<Object>{'Anna','Mary','John'});
mapWhereField.put('LastName',new Set<Object>{'Doe','Kendrick','Jane'});
mapWhereField.put('Email',new Set<Object>{'[email protected]','[email protected]','[email protected]'});

QueryString += 'Select Id,FirstName,LastName,Email FROM Contact WHERE ';
for(List<String> whereObject : whereInList) {

  Set<Object> whereData = mapWhereField.get(whereObject[0]);

  Set<String> tempWhereList = new Set<String>();    
  for(Object parsedField: whereData){
    tempWhereList.add((String) parsedField);

  //System.debug('tempWhereList : ' + tempWhereList + ' size : ' + tempWhereList.size());
  QueryString += whereObject[0] + ' IN :tempWhereList AND ';


System.debug('The WHERE QUERY' + QueryString.substring(0, QueryString.length()-4));
return Database.query(QueryString.substring(0, QueryString.length()-4));

Based on the sample code above the System debug looks like this:

Select Id,FirstName,LastName,Email FROM Contact WHERE FirstName IN :tempWhereList AND LastName IN :tempWhereList AND Email IN :tempWhereList 

When I try to run my code it return no result(0 column).The thing that i'm confuse is that I am expecting to get some result because some of this data is already in salesforce. I've tried to query it in workbench and I've seen the records. This is how I put it in the workbench.

Select Id,FirstName,LastName,Email FROM Contact WHERE FirstName IN ('Anna', 'Mary', 'John') AND LastName IN ('Doe','Kendrick','Jane') AND Email IN ('[email protected]','[email protected]','[email protected]')

My question is this, does my variable data is overriden because it has only one name ? What is the best solution to fix this using a similar concept of what I did? Please Help. Thank you

  • N.B. your third mapWhere uses the wrong key - should be 'Email'
    – cropredy
    Mar 4, 2016 at 23:33
  • @crop1645 I am sorry for that typo error. I will update my question.
    – Hope
    Mar 4, 2016 at 23:35

1 Answer 1


Well, I'll be buggered.

Apparently the variable that is declared within the for loop is still available outside the for loop. It works with if statements as well as general scope declarations.

I've created another question for those interested in following the issue of the scope of the variables: Variable scope - database.query() has access to variables not in scope

Going back to your question, I'd assume the value of tempWhereList is only calculated once; when the query is executed.

If constructing your query this way, I'd probably split it out into the various exact strings (assuming you're not working with huge amounts of data).

i.e. IN ('Anna', 'Mary', 'John') rather than IN :variable

You can achieve this by replacing:

QueryString += whereObject[0] + ' IN :tempWhereList AND ';


QueryString += whereObject[0] + ' IN (\'' + String.join(new List<String>{tempWhereList}, '\',\'') + '\') AND';

Although I'd probably re-work the script a bit.

  • @Nick Cook I am not sure if this is really what happen here. Because when I only have one value for my "whereInList" variable I will a get a correct data from my query.
    – Hope
    Mar 4, 2016 at 23:32
  • @NickCook The question was edited-- it wasn't like that before.
    – sfdcfox
    Mar 5, 2016 at 0:21
  • @sfdcfox I am really trying to figure out I am missing but when I try to put only one value "whereInList" variable. Like for example I will only add Email, the code will work . It will give a result.
    – Hope
    Mar 5, 2016 at 0:26
  • @NickCook Can you please explain what do you mean by your first suggestion ? Can you please give me what I am missing in my current code ?
    – Hope
    Mar 5, 2016 at 0:40
  • @Hope It shouldn't. Do the results actually match the criteria?
    – Nick C
    Mar 5, 2016 at 0:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .