If you’re working with Salesforce’s Apex programming language, you may need to write a query to search for specific records in the Salesforce database. In some cases, you may not know the specific values to search for at the time you write the query. In these cases, you can build a dynamic SOQL query that uses variables to specify the search criteria.

In this post, we’ll look at an example of using Database.queryWithBinds in apex and the advantages of it on the previous Database.query approach.

Example

Let’s consider the following example, where we want to find all Opportunity records that are associated with Accounts that have an industry of “Banking”, an annual revenue of less than $1,000,000, and a name that starts with “Test”.

Previously, before spring 23 release, we used to write the dynamic query code like below example.

String industry = 'Banking';
Integer AnnualRevenue = 1000000;
String name = 'Test%';
String query = 'SELECT Id, Name,Account.Name FROM Opportunity WHERE AccountId IN (SELECT Id FROM Account WHERE Industry =\''+industry+'\'';
query+= ' AND AnnualRevenue <'+AnnualRevenue;
query+= ' AND Name Like :name';
query+= ' )';
List<Opportunity> opps = Database.query(query);

In above example , we first defined three variables to specify the search criteria:

• industry: a string that specifies the industry of the associated Account
• AnnualRevenue: an integer that specifies the maximum annual revenue of the associated Account
• name: a string that specifies the name of the associated Account, using the % wildcard character to match any characters that come after the specified string
• We then build a dynamic SOQL query by concatenating strings that specify the search criteria. The query is constructed using the + operator to concatenate strings, and includes bind variables for the industry and name variables to ensure that the query is executed safely and securely.

Using Database.queryWithBinds

Using Database.queryWithBinds, the bind variables in the query are resolved from a Map parameter directly with a key rather than from Apex code variables. As a result, it’s not necessary for the variables to be in scope when the query is executed. Below is updated code for the above example –

Map<String, Object> acctBinds = new Map<String, Object>{
    'industry' => 'Banking',
    'revenue' => 1000000,
    'name' => 'Test%'
};
List<Opportunity> opps = Database.queryWithBinds(
    'SELECT Id, Name,Account.Name FROM Opportunity WHERE AccountId IN (SELECT Id FROM Account WHERE Industry = :industry AND AnnualRevenue < :revenue AND Name Like : name)',
    acctBinds,
    AccessLevel.USER_MODE
);

In the above code, we defined a Map named acctBinds that contains named bind variables for the search criteria, including:

• industry: a named bind variable that specifies the industry of the associated Account
• revenue: a named bind variable that specifies the maximum annual revenue of the associated Account
• name: a named bind variable that specifies the name of the associated Account, using the % wildcard character to match any characters that come after the specified string

We use the Database.queryWithBinds() method to execute the dynamic SOQL query. This method takes three arguments:

• The first argument is the SOQL query string, which uses named bind variables to specify the search criteria.
• The second argument is a map of named bind variables and their corresponding values. In this case, we pass in the acctBinds map we defined earlier.
• The third argument is an optional parameter that specifies the access level of the query. We use the AccessLevel.USER_MODE constant to ensure that the query is executed with the same permissions as the current user.

After executing the query, we can access the results by accessing the opps variable, which contains a list of Opportunity records that match the specified search criteria.

Reference link –

https://help.salesforce.com/s/articleView?id=release-notes.rn_apex_bind_var_soql.htm&release=242&type=5

https://ideas.salesforce.com/s/idea/a0B8W00000GdeLLUAZ/dynamic-soql-binding-expression-support-to-match-static-soql