Dynamic Queries

In a recent project, I had a requirement to run SOQL queries with dynamic object and field names. At the development time, the object name to be queried, field names to be displayed, and the even the number of fields to be queried was also not known. These were coming at runtime. My first attempt was creating a SOQL query something like

select :fieldname1,: fieldName2, :fieldName3 from :objectName

where objectName, fieldName1 etc were String  variables. Salesforce did not like this syntax at all and generated compilation error. The solution to this problem is Salesforce feature of Dynamic Query. Dynamic query allows any generic query to be performed. The complete SOQL query is defined as a String. The code below illustrates this


/*The field names are available in variables fieldName1 and fieldName2 and objectName variable defines the name of the object. Create a query string */

String queryStr='select '+ fieldName1 + ‘,’ + fieldName2  + ' from ' + objectName;

List<Sobject> results=Database.query(queryStr);

if(results == null) {

     System.debug(‘No results returned for object ‘ + objectName + ‘ field names ‘ + fieldName1 + ‘ ‘ + fieldName2);

     return null; // No work to do. So we can finish the processing.

}

// Now for all the query results we need to do processing.

for(Integer i=0; i<results.size(); i++) {

     Object returnedValue1 = results[i].get(fieldName1);

    Object returnedValue2 = results[i].get(fieldName2);

     // Since we know that the return type is that of String we can cast them appropriately.

     doProcessing((String) returnedValue1, (String) returnedValue2);

}

Creating a SOQL query at runtime using Apex code is called dynamic query. The code above is an example of Dynamic query. Dynamic query returns an sObject. This can be cast to appropriate Object type like String, Account etc. At runtime if there is a type mismatch during a cast, then a runtime exception gets generated.

Some other key concepts related to Salesforce support of dynamic queries are –

a) Dynamic queries can be used wherever the standard static SOQL query can be used.

b) Dynamic SOQL queries are constrained by the same governor limits as standard SOQL static queries.

Although dynamic queries are not a commonly used feature, in certain situations, they come in as a life-saver.