RAD Studio 10.4.1 is now available! Learn more. Looking for discounts? Visit our Special Offers page!

Secrets Of FireDAC: Dynamic Where Clause Using Conditional Substitution Macros

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.
Related posts
CodeDatabaseDelphiRAD Studio

Quickly Share Data Between Datasets Using CloneCursor In This Native Windows Sample For Delphi

DatabaseInterBaseRAD Studio

Learn How To Connecing To Local InterBase Instances In This Video

InterBaseNewsRAD Studio

Migrating from BDE/Paradox to InterBase


Get A Visually Stunning FireMonkey App Settings Template For Free Via Embarcadero GetIt

Leave a Reply

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