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

[Marcos Douglas]

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:

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

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:
===begin===
declare @paramy int = :paramy
...
and (@paramy = -1 or FieldY = @paramy)
===end===

Regards,
Marcos Douglas