8

What's the difference between using the Database.query() method versus using a soql query?

E.g. In a controller

Database.query('Select Name from MyCustomObject__c where ' + UserInfo.getUserId());


// Versus
Id id = UserInfo.getUserId();
['Select Name from MyCustomObject__c where in :id'];
1
  • database.query takes string as a parameter and it is used to do the dynamic query. And from 2 quires you posted both are strings within the string in 2 nd query you are comparing with id, since it is in quotes it will not recognize it as a actual id value and consider that as a string.
    – Suresh
    Mar 24, 2015 at 18:37

3 Answers 3

9

Primarily I've used Database.query (Dynamic SOQL) when:

  • I'm creating a managed package and don't want to take an explicit dependency on a feature. E.g. List<sobject> revenueSchedules = Database.query('Select Id from OpportunityLineItemSchedule where OpportunityLineItemId in :oliIds'); If I did this in static SOQL all the installers of the managed package would need Product Schedules enabled.
  • I want to dynamically include an optional field or filter in the SOQL query. A custom setting may define the name of a field that should be used to populate something else. As it isn't know at "compile" time it needs to be dynamic.

Otherwise I'd generally use static SOQL to cut down on ceremony code.

As the other answers and comments point out, dynamic SOQL limits your binding options. You can do simple bindings, such as checking if an Id is in a Set. However, you can't do more complex bindings that traverse properties. Instead you will need to convert these to strings. See Salesforce Ideas: Dynamic SOQL binding expression support to match static SOQL

Note that you need to be more careful with dynamic SOQL that you don't open yourself up to SOQL Injection.

2
  • Is there any performances issues between using Dynamic SOQL and SOQL ?
    – chrisjlee
    Mar 24, 2015 at 20:27
  • I'm not aware of any performance difference of not between the two. I'd always go with the easiest to understand unless there is a compelling reason not to. I.e. Use static SOQL unless you explicitly need something that dynamic SOQL gives you. Mar 24, 2015 at 20:29
0

In additoion to my above comment: In some of the cases your condition changes dynamically for example based on user profile you may need to change the query condition in such scenarios you need the dynamic query(database.query), when your where clause (condition) remains same always then you may prefer[Selct Id from Contact ...........].

0

From Salesforce help:

Database.query can be used wherever a static SOQL query can be used, such as in regular assignment statements and for loops. Unlike inline SOQL, fields in bind variables are not supported.

So the fundamental difference is the lack of support for variable binding.

1
  • 1
    It's not a lack of all variable binding, only "complex" binding. You can still bind to a list, a set, or any primitive type that can coerced into the field's data type.
    – sfdcfox
    Mar 24, 2015 at 19:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.