Query pass null parameter

Question

I can pass the value is null or is not null through a Query? Example in SQL:

select * from tb_teste where tb_teste.data_hora :PARAMETRO;

At Delphi:

Query.ParamByName('PARAMETRO').value = 'is not null';

or

Query.ParamByName('PARAMETRO').value = 'is null';

Note: the time_time field is a Timestamp, I am using Firebird database.

Answer 1

Apparently you want to list either the records with null field OR the non-null ones, right? The parameter will never be a date whose value will be applied in the query filter.

If this is the case I would do the following:

In the query:

select * from tb_teste where 
(
    (:param = 'nulos' and tb_teste.data_hora is null ) 
 or (:param = 'preenchidos' and tb_teste.data_hora is not null)
);

In Unit:

Query.ParamByName('param').AsString = 'nulos';

or

Query.ParamByName('param').AsString = 'preenchidos';

Answer 2

If you go with Firedac’s Querys you can, using macros instead of params In query uses

select * from tb_teste where tb_teste.data_hora !PARAMETRO;

and in the code

Query.MacroByName('PARAMETRO').value = 'is not null';

If error validates the macro type in the query, I use mdRaw but I assume it also works with mdString

Answer 3

How to load "null" parameters in Query Insert in Delphi

Insert into tabela (campostr, campoint, campocur, campodat) Values (:vstr, :vint, :vcur, :vdat);

ParamByName('vstr').DataType := ftString;  

if varstrnull then ParamByName('vstr').Clear 
else ParamByName('vstr').AsString := 'string';

ParamByName('vint').DataType := ftInteger;

if varintnull then ParamByName('vint').Clear 
else ParamByName('vint').AsInteger := 1;

ParamByName('vcur').DataType := ftCurrency;

if varcurnull then ParamByName('vcur').Clear 
else ParamByName('vcur').AsCurrency := 1,25;

ParamByName('vdat').DataType := ftDate;

if vardatnull then ParamByName('vdat').Clear 
else ParamByName('vdat').AsDate := now;

Obs.: Include use DB.

Answer 4

You can do so within the query:

select * from xpto where 1=1
and ((parametro = :parametro) or (parametro = -1))

ai when it is null you pass -1

Answer 5

You will need to make some adaptations to achieve, in the current format you want is NOT possible, because:

select * from tb_teste where tb_teste.data_hora FALTA_OPERADOR :PARAMETRO;

The operator is missing, that is, when executing this query will already have sql error -104.

What you can do is set a string to replace, think of something like:

if alguma_coisa then
  vTemp := 'is not null'
else
  vTemp := 'is null';

vSQL := select * from tb_teste where tb_teste.data_hora &Troca& vSQL := vSQL.Replace('&Troca&', vTemp);