Have an amazing solution built in RAD Studio? Let us know. Looking for discounts? Visit our Special Offers page!

Secrets Of FireDAC: Dynamic Where Clause Using Conditional Substitution Macros

the future of starts demands massive productivity

If you are using FireDAC and building your SQL queries manually you may be using more verbose code than is needed. FireDAC has a feature called Macros which allows you to do a variable substitution in your SQL query similar to parameter substitution but for raw SQL. However, in addition to macro substitution you can combine it with conditional substitutions which lets you do the real magic.

If you are building your SQL query from scratch using strings you probably have some logic that decides whether to show the WHERE clause or not. With SQL you can’t have a blank WHERE clause which leaves you writing some logic to handle that. FireDAC allows you to handle this situation very elegantly by using a conditional substitution IF statement within your SQL string.

Instead of concatenating your SQL string together you can simply write {IF !Where} WHERE !Where {FI} in your SQL statetment and then assign the macro using AsRaw (see below).

// If user entered some condition into Edit1, then the content will be substituted into SELECT WHERE
FDQuery1.SQL.Text := 'SELECT * FROM Customers {IF !Where} WHERE !Where {FI}';
FDQuery1.MacroByName('Where').AsRaw := Edit1.Text;




Reduce development time and get to market faster with RAD Studio, Delphi, or C++Builder.
Design. Code. Compile. Deploy.
Start Free Trial   Upgrade Today

   Free Delphi Community Edition   Free C++Builder Community Edition

About author

FMXExpress.com has over 600 articles with all kinds of tips and tricks for Delphi FireMonkey on Android, IOS, OSX, Windows, and Linux.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.