How to Use Bind Variables in Salesforce SOQL? : Bijay Kumar

How to Use Bind Variables in Salesforce SOQL?
by: Bijay Kumar
blow post content copied from  SalesForce FAQs
click here to view original post



### Summary of Variable Binding in Salesforce SOQL In Salesforce, **variable binding** is a technique that allows developers to use variables from Apex code directly in SOQL (Salesforce Object Query Language) queries. By using a colon (:) before a variable name, you can create dynamic queries that adapt based on the current value of the variable. #### Key Points: - **Dynamic Queries**: Variable binding transforms static SOQL queries into dynamic ones, allowing for more flexible data retrieval. - **Usage**: Bind variables can be used in various clauses of SOQL, including: - **WHERE** clauses for filtering data - **FIND** clauses for search strings - **IN/NOT IN** operators for dynamic sets of values - **LIMIT** and **OFFSET** clauses for controlling the number of records returned - **Examples**: - Fetching contact details based on a dynamically set first name. - Querying account details using conditions that involve arithmetic comparisons. - Retrieving records with limits and offsets based on variable values. #### Example Scenarios: 1. **Simple Binding**: Fetching account details using a variable for the account ID. 2. **Arithmetic Operators**: Querying accounts based on revenue conditions. 3. **Dynamic Filters**: Using multiple bind variables for filtering accounts by type and location. 4. **Limit and Offset**: Dynamically controlling the number of records retrieved and skipping a set number of records. ### Conclusion Variable binding in SOQL enhances the ability to create flexible and efficient queries in Salesforce, making it easier to work with dynamic data. ### Additional Information Understanding variable binding is crucial for Salesforce developers who want to optimize their queries and improve application performance. It allows for more readable and maintainable code by reducing hard-coded values. ### Relevant Hashtags for SEO #Salesforce #SOQL #VariableBinding #Apex #SalesforceDevelopment #DynamicQueries #SalesforceTutorial #Coding #DataRetrieval #SalesforceBestPractices


In Salesforce, variable binding is a method of using variables defined in the Apex code directly within a SOQL query. The variable binding allows us to dynamically set values in the query based on the variable’s current value, usually indicated by a colon (:) before 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 Apex variables in Salesforce SOQL queries.

What is Variable binding in Salesforce SOQL?

In Salesforce, SOQL Variable Binding is done when we need to use a variable value in the query as a part of any SOQL condition. In this, we make a static SOQL query into a dynamic one. For example, we executed an SOQL query to fetch the contact details of any specific user, for that, we will execute the query in the following way.

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

This SOQL query will only give details for specific contacts, but suppose we can make the input string dynamic where it can take firstName dynamically, and to do this, we use the Apex binding variable by using a colon (:).

We can use the Apex variable and bind them 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 the SOQL queries, the bind variables can be used for 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 value in LIMIT clauses.
  • For numeric value in OFFSET clauses.

Binding Apex Variables in Salesforce SOQL Queries

In Salesforce, SOQL statements can reference Apex code variables and expressions if they’re followed by a colon like (: value).

Example-1:

Let’s take an example where we have to dynamically query the Account details for a specific account. 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 below methods, we will see the binding of the SOQL statements for different scenarios using the bind variable.

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);
SOQL binding in Salesforce Apex

Output:

Bind in Salesforce SOQL using Apex

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, we will take another example: where to find accounts with minimum locations for the specific account type. Both filters must be dynamic as the minimum amount 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 the bind variables in the WHERE clause for the account type and minimum location as “:accountType” and “:minimumLocations.”

Output:

How to bind SOQL query in Salesforce

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 it via 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 will retrieve the 10 most recently created Account records with ID and Name fields using the ORDER BY CreatedDate DESC clause. Then, the LIMIT :numberOfAccounts ensures that the number of retrieved records is dynamically controlled by the variable.

Output:

How to bind SOQL query in Salesforce

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 the method to bind the variable for 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 redined 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:

Salesforce Offset to bind variables

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 use examples of the SOQL bind queries, we have learned how variable binding can be applied in different scenarios, such as filtering by dynamic values, using arithmetic comparisons, limiting query results, handling substring values, and implementing pagination with the OFFSET clause.

You may also like to read:

The post How to Use Bind Variables in Salesforce SOQL? appeared first on SalesForce FAQs.


February 03, 2025 at 09:42AM
Click here for more details...

=============================
The original post is available in SalesForce FAQs by Bijay Kumar
this post has been published as it is through automation. Automation script brings all the top bloggers post under a single umbrella.
The purpose of this blog, Follow the top Salesforce bloggers and collect all blogs in a single place through automation.
============================

Salesforce