Salesforce Dynamic SOQL

What Is Dynamic SOQL In Apex?

Dynamic SOQL means the creation of SOQL string at runtime with Apex code. It is basically used to create more flexible queries based on the user’s input.

Query Method Of The Database Class

The query method is used to fetch records from the database. We need to pass the SOQL query string in this method to fetch the records.

For example,

Database.query(queryString);

Example of Dynamic SOQL in salesforce

public static void main(String objectName) {
                String queryString = ‘SELECT Name FROM’ + objectName;
                List < sObject > sList = Database.query(queryString);
                for (sObject s: sList) {
                    System.debug(s);
                }
            }

Note:

If the query string is wrong, this method returns the QueryException.

Variable binding in Dynamic SOQL

We can use bind variables (:i) in dynamic SOQL but we can’t use bind variable field in a dynamic SOQL. ‘:500’

public static void main() {
                String s = ‘Test % ’;
                Database.query(‘SELECT Name FROM Account WHERE Name LIKE: s’);
         }

The above query will work in dynamic as well as static SOQL.

public static void main() {
                Account a = new Account(Name = ‘Test’, Phone = ’12345’);
                Database.query(‘SELECT Name FROM Account WHERE Phone = : a.Phone’);
            }

The above query will not work in dynamic SOQL and will result in a ‘variable does not exist’ error in it. But it will work perfectly in static SOQL.

There is a hack to use bind variable fields in SOQL.

public static void main() {
                Account a = new Account(Name = ’abcd’, Phone = ’12345’);
                String str = a.Phone;
                String s = ‘SELECT Name FROM Account WHERE phone = : str’;
            }

Note:

Use string escape singleQuotes(String str) on the string used for creating the query on dynamic SOQL, just to prevent SOQL injection.

 String.escapeSingleQuotes(‘SELECT Name FROM Account WHERE phone = : str’);
 Database.query(finalString);

This method replaces all single quotes(‘) by (\’) which ensures that all single quotation marks are treated as enclosing strings instead of database commands.

String fieldString = 'Name';
            String sObjectString = 'Position__c';
            List < Position__c > positionList = Database.query('SELECT ' + fieldString + 'FROM ’ + sObjectString);