Understanding Dynamic SOQL in Salesforce: A Simple Guide with Examples

Understanding Dynamic SOQL in Salesforce

By Chinmaya December 23, 2024

6 Min Read

Contents

Introduction What is Dynamic SOQL?Why We Use Dynamic SOQL?How to Use Dynamic SOQL?Syntax of Dynamic SOQL Example 1: Fetch Accounts Based on User Input Static SOQL:Dynamic SOQL Example 2: Querying Dynamic Fields Example 3: Querying Any Object Best Practices for Dynamic SOQL When NOT to Use Dynamic SOQL?Conclusion Bonus: Full Code Example

Introduction

Salesforce’s Dynamic SOQL is a powerful feature that allows you to construct and execute SOQL (Salesforce Object Query Language) queries at runtime.

This flexibility is particularly useful when the structure of your query depends on user input or specific conditions.

In this blog, we’ll explore:

1. What is dynamic SOQL?
2. when to use Dynamic SOQL in Salesforce?
3. Explain how Dynamic SOQL works with clear example.

What is Dynamic SOQL?

To understand what is Dynamic SOQL in Salesforce, lets understand what is Static SOQL ?

In Salesforce, Static SOQL queries are defined at compile time. For instance:

				
					List<Account> accounts = [SELECT Id, Name FROM Account WHERE Industry = 'Technology'];

In contrast, Dynamic SOQL allows you to build the query as a string at runtime.
This means you can decide the fields, filters, or even the object to query dynamically.

Why We Use Dynamic SOQL?

Here are some common scenarios where dynamic SOQL is helpful:

Customizable queries: The query structure can change based on user inputs or business logic.
Dynamic fields: Fetch fields that are determined at runtime.
Reusable code: Build general methods for querying different objects.

How to Use Dynamic SOQL?

Dynamic SOQL uses string concatenation to build the query.
It is written as a string and executed using the Database.query() method.

Let’s look at a simple example.

Syntax of Dynamic SOQL

				
					String query = 'SELECT fields FROM Object WHERE conditions';
List<SObject> results = Database.query(query);

Example 1: Fetch Accounts Based on User Input

Imagine a scenario where you want to fetch Accounts based on an industry that the user selects.

Static SOQL:

				
					String selectedIndustry = 'Technology';
List<Account> accounts = [SELECT Id, Name FROM Account WHERE Industry = :selectedIndustry];

Dynamic SOQL

				
					String selectedIndustry = 'Technology';
String query = 'SELECT Id, Name FROM Account WHERE Industry = \'' + selectedIndustry + '\'';
List<Account> accounts = Database.query(query);

Here, the query is built as a string and executed at runtime.

Example 2: Querying Dynamic Fields

Suppose you have a custom field name stored in a variable, and you need to include it in your query.

				
					String dynamicField = 'AnnualRevenue';
String query = 'SELECT Name, ' + dynamicField + ' FROM Account';
List<Account> accounts = Database.query(query);

Now the query dynamically includes the AnnualRevenue field.

Example 3: Querying Any Object

If you’re building a generic method to query any object, dynamic SOQL is the way to go.

				
					public List<SObject> fetchRecords(String objectName, String fields, String condition) {
    String query = 'SELECT ' + fields + ' FROM ' + objectName;
    if (!String.isEmpty(condition)) {
        query += ' WHERE ' + condition;
    }
    return Database.query(query);
}

// Usage
List<SObject> accounts = fetchRecords('Account', 'Id, Name', 'Industry = \'Technology\'');

This method can query any object dynamically based on the parameters you pass.

Best Practices for Dynamic SOQL

- Avoid SOQL Injection: When building queries, sanitize inputs to prevent malicious SQL-like injections. Use bind variables whenever possible.
  Example:

				
					String industry = 'Technology\' OR Industry != null'; // Malicious input
String query = 'SELECT Id, Name FROM Account WHERE Industry = \'' + String.escapeSingleQuotes(industry) + '\'';

- Keep Queries Simple: Building overly complex queries dynamically can make debugging difficult.
- Combine with Static SOQL: Use static SOQL whenever the query structure is fixed. Use dynamic SOQL only when necessary.
- Limit Returned Records: Always include LIMIT or specific filters to avoid hitting governor limits.

When NOT to Use Dynamic SOQL?

- Performance-Critical Code: Dynamic SOQL is slightly slower than static SOQL.
  Always prefer static SOQL for better performance and readability.
- Simple Use Cases: If the query structure is fixed, prefer static SOQL for better readability and maintainability.

Conclusion

Dynamic SOQL is an excellent tool for scenarios where your query structure needs to adapt to runtime conditions. By using Database.query() and string manipulation, you can build versatile, reusable solutions. However, always be cautious of SOQL injection risks and Salesforce governor limits.

Dynamic SOQL opens the door to advanced functionality, making your Apex code more flexible and powerful. Start experimenting with it in your Salesforce projects today!

Bonus: Full Code Example

Here’s a complete Apex class demonstrating dynamic SOQL:

				
					public class DynamicSOQLExample {
    public static List<SObject> fetchDynamicRecords(String objectName, String fields, String condition) {
        try {
            String query = 'SELECT ' + fields + ' FROM ' + objectName;
            if (!String.isEmpty(condition)) {
                query += ' WHERE ' + condition;
            }
            return Database.query(query);
        } catch (Exception e) {
            System.debug('Error in dynamic SOQL: ' + e.getMessage());
            return new List<SObject>();
        }
    }
    
    public static void exampleUsage() {
        String objectName = 'Account';
        String fields = 'Id, Name';
        String condition = 'Industry = \'Technology\'';
        
        List<SObject> results = fetchDynamicRecords(objectName, fields, condition);
        for (SObject record : results) {
            System.debug('Record: ' + record);
        }
    }
}