Secrets Of FireDAC: Dynamic Where Clause Using Conditional Substitution Macros

by Jul 20, 2018

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;
FDQuery1.Open;