[fpc-pascal] Create dynamic SQL according to available params

Marcos Douglas md at delfire.net
Thu Apr 7 15:04:24 CEST 2016

On Thu, Apr 7, 2016 at 9:00 AM, Luiz Americo Pereira Camara
<luizamericop at gmail.com> wrote:
> I enconter the following pattern frequently (simplified):
> SQL:
> Select * From Customers Where FieldX = 1
> Later i need a similar query that uses a different filter like
> Select * From Customers Where FieldX = 1 and FieldY = :paramy
> Is there any code that given a SQL Template would generate the second filter when paramy is available and keep blank when not available?

You can do this:

select *
from Customers
where 1=1
and FieldX = 1
and (:paramy = -1 or FieldY = :paramy)

if you do not want to filter, just pass -1 (or whatever). Otherwise,
pass the real value.

For MSSQL I like to declare a variable like this:
declare @paramy int = :paramy
and (@paramy = -1 or FieldY = @paramy)

Marcos Douglas

More information about the fpc-pascal mailing list