SOQL Query Dynamic Variable Binding

Introduction

Ever since I started diving deeper into Salesforce, I’ve been fascinated by how SOQL dynamic variable binding can completely transform the way we approach application development. It’s this incredible feature that allows us to tailor our data queries on the fly, making our applications not just more flexible but also way more efficient. Honestly, understanding and applying this technique has been a game changer for ensuring our Salesforce projects are perfectly aligned with business needs, no matter how complex they get.

When, Where, and How to use it

You can use dynamic variable binding whenever you want to write a dynamic query. Please be careful because dynamic queries may not be secure due to SOQL injections. How it worked before:

String myVariable = 'TestName';
List<sObject> sobjList = Database.query('SELECT Id FROM Account WHERE Name = :myTestString';

this piece of code should work correctly because it uses auto-binding and Apex allows us to do 1-level deep binding without any restrictions, variable should be just in scope.

With new Database.queryWithBinds() methods you are able to build a map (Map<String, Object>) of bindigns variables and pass it to map with your query variables. Also the AccessLevel should be passed, it can be AccessLevel.USER_MODE and AccessLevel.SYSTEM_MODE

Note: By default, Apex code runs in system mode, which means that it runs with substantially elevated permissions over the user running the code. In system mode, the object and field-level permissions of the current user are ignored, and the record sharing rules are controlled by the class sharing keywords. In user mode, the current user’s object permissions, field-level security, and sharing rules are enforced.

Code examples

Map<String, Object> queryBinds = new Map<String, Object>{
                'accountName' => 'MyName',
                'accountStatus' => AccountConstant.DEACTIVATED,
                'accountNumber' => getAccountNumber(),
                'accountType' => AccountConstant.DEACTIVATED.left(1),
                'accountActive => true
};

String query = 
    'SELECT Name, Status, Number, Type, Active ' +
    ' FROM Account ' +
    ' WHERE Name = :accountName AND Status = :accountStatus AND ' +
    ' Type != :accountType AND Number > :accountNumber ';

List<Account> accounts  = Database.queryWithBinds(
        query,
        queryBinds,
        AccessLevel.USER_MODE
);