How to Use Bind Variables in Salesforce SOQL?

In Salesforce, variable binding is a method of using variables defined in the Apex code directly within a SOQL query.

Variable binding enables us to dynamically set values in the query based on the variable’s current value, typically indicated by a colon (:) preceding the variable name within the query string.

In this Salesforce tutorial, I will explain what variable binding is in Salesforce SOQL and how to bind the variables in Salesforce SOQL queries.

Table of Contents

What is Variable Binding in Salesforce SOQL?

In Salesforce, SOQL Variable Binding is used when we need to utilize a variable value in the query as part of any SOQL condition.

Here, we convert a static SOQL query into a dynamic one. For example, we executed an SOQL query to retrieve the contact details of a specific user; to do this, we executed the query in the following manner.

SELECT firstName, lastName, Email from Contact WHERE firstName = 'david'

This SOQL query will only provide details for specific contacts. However, suppose we can make the input string dynamic, allowing it to take the firstName dynamically.

To achieve this, we use the Apex binding variable by using a colon (:).

We can use the Apex variable and bind it with SOQL in the following way.

String accountName = 'David ';

List<Contact> contacts = [SELECT firstName, lastName, Email from Contact WHERE firstName = ':accountName];

By using the above code, we can dynamically query contacts by name. We can also have multiple SOQL bind variables in a query and do different comparisons.

For SOQL queries, bind variables can be used in the following scenarios.

As a search string in FIND clauses.
As filter literals in WHERE clauses.
The value of the IN or NOT IN operator in WHERE clauses allows filtering on a dynamic set of values.
For the division in names by using the WITH DIVISION clauses.
For numeric values in LIMIT clauses.
For numeric values in OFFSET clauses.

Binding Apex Variables in Salesforce SOQL Queries

In Salesforce, SOQL statements can reference Apex code variables and expressions if a colon (: value follows them).

Example-1:

Let’s consider an example where we need to query account details for a specific account dynamically. For this, we can create an assignment to insert an account, and referring to the ID of the inserted account, we can query it in the SOQL query using the bind variable (:).

In the methods below, we will examine the binding of SOQL statements for various scenarios using bind variables.

Simple binding in SOQL with Text Value:

Account A = new Account(Name = 'Burlington Textiles Corp of America');
insert A;

Account B;
B = [SELECT Id, Name FROM Account WHERE Id = :A.Id];
System.debug ('Retrived Account: ' +B);

Output:

This way, we can dynamically assign the account name in the above SOQL query using the Apex binding variable.

Binding SOQL with Arithmetic Operators:

Account A = new Account(Name = 'Burlington Textiles Corp of America', AnnualRevenue = 50000);
insert A;
Account B;
B = [SELECT Id, Name, AnnualRevenue FROM Account 
     WHERE Id = :A.Id AND AnnualRevenue > :A.AnnualRevenue - 10000];
System.debug('Retrieved Account: ' + B);

In the above code, we created and inserted an Account (A) with an annual revenue of 50000. Then we have queried for the same account, but with an additional condition:
The AnnualRevenue must be greater than A.AnnualRevenue – 10000.

Then, if A.AnnualRevenue = 50000, then the SOQL effectively checks:
WHERE AnnualRevenue > 40000.

Example-2:

Now, let’s consider another example: where to find accounts with the minimum number of locations for a specific account type.

Both filters must be dynamic as the minimum number of locations depends on the type of our business case. Therefore, we will use two bind variables in our query. One compares the type, while the other compares the number of locations.

String accountType = 'Customer - Direct'; 
Integer minimumLocations = 3; 

List<Account> accounts = [SELECT id, NumberofLocations__c, Type, name FROM Account 
                          WHERE Type = :accountType AND NumberofLocations__c >= :minimumLocations];

for (Account acc : accounts) {
    System.debug('Account Name: ' + acc.Name + ', Type: ' + acc.Type + ', Number of Locations: ' + acc.NumberofLocations__c);
}

In the above code, we have used bind variables in the WHERE clause for the account type and minimum location, denoted as “:accountType” and “:minimumLocations.”

Output:

Example-3:

Another use case of binding variables in Salesforce SOQL can be retrieving the most recent object records with a limit. Here, we can define the limit in a variable, and using the binding variable, we can refer to the limit in the SOQL query.

For that, we can define the limit and refer to it via a binding variable in the following way:

Integer numberOfAccounts = 10;
System.debug('Number of Accounts: ' + numberOfAccounts);

List<Account> accounts = [SELECT id, name FROM Account ORDER BY CreatedDate DESC LIMIT :numberOfAccounts];

System.debug('Retrieved Accounts: ' + accounts);

The above SOQL query retrieves the 10 most recently created Account records, including their ID and Name fields, using the ORDER BY CreatedDate DESC clause. Then, the LIMIT:numberOfAccounts ensures that the variable dynamically controls the number of retrieved records.

Output:

This way, we can dynamically assign integer values in the Salesforce SOQL queries using the binding variables.

Example-4:

In this example, we will see how to bind a variable to the substring value.

String name = 'NewAccount-Delphi Chemicals';
String refinedName = name.substring(11);

List<Account> accounts = [SELECT Id, Name FROM Account WHERE Name = :refinedName];
System.debug('Queried Accounts: ' + accounts);

This query will only return the Account name, skipping the characters that we refined in the substring.

Example-5:

We also bind SOQL in Apex code using the OFFSET clause.

Integer offSetNumber = 5;
List<Account> accounts = [SELECT Id, Name FROM Account OFFSET :offSetNumber];
System.debug('Offset Number: ' + offSetNumber);
System.debug('Number of Accounts Retrieved: ' + accounts.size());
System.debug('Retrieved Accounts: ' + accounts);

Output:

With the offset clause, the query will leave the first five accounts and fetch the rest. Using this binding SOQL query, we can dynamically change the value of the OFFSET number.

Conclusion

In this Salesforce tutorial, we have learned about binding variables and how to use them in SOQL to make the SOQL query dynamic using the colon (:) syntax.

In the above examples of SOQL bind queries, we have learned how variable binding can be applied in various scenarios, including filtering by dynamic values, performing arithmetic comparisons, limiting query results, handling substring values, and implementing pagination with the OFFSET clause.

How to Use Bind Variables in Salesforce SOQL?

What is Variable Binding in Salesforce SOQL?

Binding Apex Variables in Salesforce SOQL Queries

Conclusion

DOWNLOAD FREE AGENTFORCE EBOOK

FREE SALESFORCE FLOW EBOOK