Dynamic SOQL query

Question

I'm trying to build a dynamic query but I think I may have the wrong syntax.

String query = 'SELECT ProjectId__c from Project__c'
projectList = Database.query(query + ' WHERE Name__c equals 'pp2');

No error is thrown only there are no retrieved records. Also, can someone provide an example of adding multiple arguments to a dynamic query?

Answer 1

Your code has a few syntax errors, but my assumption is that what you want is:

String query = 'SELECT ProjectId__c from Project__c';
List<sObject> projectList = Database.query(query + ' WHERE Name__c = \'pp2\'');

However, a very nice feature of dynamic SOQL is that it supports referencing local variables like static SOQL, so you could make your query:

String query = 'SELECT ProjectId__c from Project__c', nameToSearch = 'pp2';
List<sObject> projectList = Database.query(query + ' WHERE Name__c = :nameToSearch');

Answer 2

You should not use a dynamic query unless you must. In this case, since you are doing nothing fancy, just use a static query:

projectList = [SELECT ProjectId__c FROM Project__c WHERE Name = 'pp2'];

---

There are two problems you have, the first of which is much easier to solve.

1. The equality operator for SOQL is =, not equals.

2. If you want to merge in a literal string, you must escape the single quote characters:

   projectList = Database.query(baseQuery + 'WHERE Name__c = \'pp2\'');
   

3. As an alternative to 2, you can use merge syntax. You can merge in any variable that is in scope, but you cannot reference any names or properties.

   projectList = Database.query('SELECT ProjectId__c FROM Project__c WHERE Name = :pp2');
   

Answer 3

Firstly, you need to use = instead of the word equals in the query.

Secondly, is pp2 a variable name, or the actual text you want to match?
If it's a variable, you need to concat it into the string like ' Where Name__c = '+pp2
If it's the literal text, you need to escape the single quotes like so, ' WHERE Name__c = \'pp2\''