Static and Dynamic SOQL

SOQL Tutorial What is SOQL SOQL Real Life Scenario SOQL Naming Conventions SOQL WHERE SOQL Comparison Operators SOQL Logical Operators SOQL ORDER BY SOQL LIMIT SOQL OFFSET SOQL Aggregate Functions SOQL GROUP BY SOQL HAVING SOQL Relationship Queries SOQL Date Literals SOQL Date Functions SOQL History Objects SOQL Sharing Objects SOQL in APEX Apex Variables in SOQL Queries Static and Dynamic SOQL SOQL return types SOQL Governor Limits Efficient SOQL Queries

We have two ways to invoke SOQL queries in Apex code: Static and Dynamic SOQL.

Static SOQL

Well, if you have read the examples in the previous sections, you already know about Static SOQL. The Static SOQL query is written inside square brackets and returns a list of records meeting the criteria. Thus while writing the query, you know which object to query, which fields to query, and what conditions to apply. The only thing that can vary is the use of Bind Expressions.

Example: Fetch Name, max_no_of_appointments__c of all the clinics that are in Florida.

List<Clinic__c> clinics = [SELECT Id,Name,max_no_of_appointments__c  
  from clinic__c
  where state__c = 'Florida'];

Here we knew we had to query on clinic__c, and which fields to fetch, and which fields to apply filter conditions on.

Dynamic SOQL

In Dynamic SOQL, we generate the SOQL query at run-time as a string. The name of the object, the names of the field need not be known in advance. Therefore these can be used to design more flexible applications when the fields to apply filters on are not known in advance.

Dynamic SOQL can be invoked by Database.query(query_string); where query_string is the query generated at runtime. In operation and processing, it works the same as Static SOQLs.

Example :

List<Contact> conList = Database.query('SELECT Id, Name
  FROM contact
  WHERE firstname= \'James\' ');

Did you see that to compare strings or any quoted value, we need to escape the quotes with the backslash, as the query is also written within quotes. Eg 'James'.

Another example where fields are not known in advance:

String fieldnames = 'Id, Name, Email';
String query = 'SELECT '+ fieldNames + ' FROM Contact';
List<Contact> conList = database.query(query);

Here field names are supplied externally. This could have easily been an apex function which accepts fieldnames as a parameter.

Dynamic queries also support bind expressions in the same syntax as static SOQL. However, only simple bind expressions are supported. Bind expressions from an object’s field are not supported.

Example of a query that works perfectly fine:

String str = 'James';
database.query('SELECT Id FROM Contact WHERE firstname = :str ');

Example of a query that throw a a runtime error.

Contact con;
database.query('SELECT Id FROM Contact WHERE firstname = :con.firstname');

Key Things to Note

• Any compile-time errors are not thrown while saving a dynamic query.
• You have to be extremely cautious to prevent runtime errors.
• Dynamic SOQL can also cause potential security threats through SOQL injection. A malicious user might intentionally pass some characters which can retrieve more SOQL results that intended. If you must use dynamic SOQL, use the escapeSingleQuotes method to sanitize user-supplied input. This method adds the escape character () to all single quotation marks in a string that is passed in from a user. The method ensures that all single quotation marks are treated as enclosing strings, instead of database commands.