Dynamic SOQL in Salesforce

In this article you will learn about Dynamic SOQL and how to write dynamic SOQL in Salesforce.

What is Dynamic SOQL?

Dynamic SOQL refers to the ability to create and execute SOQL queries at runtime, rather than hardcoding them into your Salesforce Apex code. This provides flexibility in querying salesforce data based on varying criteria or conditions.

How to write Dynamic SOQL?

We can either use Database.query or Database.queryWithBinds methods to create dynamic SOQL query at run time. Let’s understand both the methods one by one.

Dynamic SOQL using Database.query 

Lets execute a simple query using Database.query method. Execute below code from anonymous window. 

String name = 'Dickenson%'; 
List<sobject> sobjectList = Database.query('Select id,name from Account where name LIKE :name');
System.debug(sobjectList);

When you execute this code and open debug logs you will see the list of account records with a name like Dickenson like in the image below. 

Now try executing the below code from an anonymous window.

Account a = new Account(Name='Genepoint');
List<sobject> sobjectList = Database.query('Select id,name from Account where name LIKE :a.Name');
System.debug(sobjectList);

It will give you an exception like the image below because here we are trying to bind variable fields in the query string but the same code will work if you use static SOQL.

The above error can be resolved by storing the bind variable field into a variable. Lets execute the below code.

Account a = new Account(Name='Genepoint');
String name = a.name;
List<sobject> sobjectList = Database.query('Select id,name from Account where name LIKE :Name');
System.debug(sobjectList);

If you open the debug log you will see the list of account records where names like Genepoint.

The above problem of QueryException: Variable does not exist can also be resolved by using Database.queryWithBinds instead of Database.query. Lets learn about Database.queryWithBinds in the next section.

Learn about Dynamic Apex in Salesforce.

Dynamic SOQL using Database.queryWithBinds

Let’s first look at the syntax of this method.

List<sObject> sobjList = Database.queryWithBinds(string, bindVariablesMap, accessLevel); 

As you can see in above code snippet this method expects 3 parameter first parameter is query string which you want to execute, second parameter is a map of bindvariables and the third parameter is accessLevel which means you want to execute the query in USER_MODE or SYSTEM_MODE. In USER_MODE the object permissions and field level security along with sharing rule of logged in user is enforced while in SYSTEM_MODE the object permissions and field level security of the logged in user are ignored.

Let’s execute the code below from an anonymous window.

Map<String, Object> accountBinds = new Map<String, Object>{'accountName' => 'GenePoint'};
List<Sobject> sobjectList = Database.queryWithBinds('SELECT Id, Name FROM Account WHERE Name = :accountName',accountBinds,AccessLevel.USER_MODE);
System.debug(sobjectList);

Here is the log which you will see when you open debug logs.

In the above code we created a map of bind variable where we stored key value pairs which will be used for filtering records then we used Database.queryWithBinds method where we passed a soql query, map of bind variable and accesslevel.

However, it’s important to note that using dynamic SOQL can make your code more complex and potentially introduce security vulnerabilities such as SOQL injection if not handled properly. Always ensure to properly sanitize and validate user input when constructing dynamic queries to prevent such security risks.

I hope now you know how to write dynamic SOQL and when to use Database.query and Database.queryWithBinds method. To learn more about Dynamic SOQL checkout Apex developer guide official doc from here.