Using Variables In Dynamic SOQL - Uncovering



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!.





2 comments:

  1. this really helped

    ReplyDelete
  2. Good one. Just came across while digging something else. Thanks - Venkat

    ReplyDelete