Using Variables In Dynamic SOQL - Uncovering

Monday, September 10, 2012

In Earlier post "Using Variables In Dynamic SOQL",  I wrote about how to use variables in a Dynamic SOQL. Well after playing with Dynamic SOQL, I found few interesting thing like:

·         You CAN directly use primitive data types in SOQL.

So what does this means?

This means you can directly use any Integer, String , Date, Datetime,Double, Id variable along with Collection of this variable within the query.

For Example: 

a) You are allowed to do this

//initialize the Datetime with current time

DateTime now = System.now();

//query accounts by merging the variable name inside the query string

List<Account> accountList = Database.query('SELECT Id FROM Account WHERE CreatedDate =:now');

b)You can also include LISTs(Collections) in your queries

List<String> accNameList = new List<String>{'Acc1','Acc2'}

//query accounts by merging the variable name inside the query string

List<Account> accountList = Database.query('SELECT Id FROM Account WHERE Name IN:accNameList');

·         You CANNOT use complex types in a Dynamic SOQL directly.

This means that you cannot use any Sobject, Apex Class or Any other user defined data type inside the Dynamic Query. In short you cannot use a dot (".") operator to specify a field value in a Dynamic query.

For Example :

                a)  Using an Sobject inside the query String will not work

           //initialize a Account with a Name value for demo

Account acc = new Account(Name='MyAccount');

//query accounts by merging the variable name inside the query string

//This will not work

List<Account> accountList = Database.query('SELECT Id FROM Account WHERE Name =:acc.Name');

//But You can always make the above work by writing the code as.

           //initialize a Account with a Name value for demo

      Account acc = new Account(Name='MyAccount');

      String accountName = acc.Name

      //query accounts by merging the variable name inside the query string

      List<Account> accountList = Database.query('SELECT Id FROM Account WHERE                       
      Name =: accountName  ');

Hope this helps!.