Query pass parameter null

-1

Can I 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;

In Delphi:

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

or

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

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

    
asked by anonymous 16.10.2018 / 21:06

5 answers

1

Apparently you want to list OR records with null field OR non-null, 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';
    
16.10.2018 / 21:34
0

You can do this within the query:

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

Then when null you pass -1

    
16.10.2018 / 21:13
0

You will need to make some adjustments to achieve, in the current format that you want 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 you will already have a sql -104 error.

What you can do is to define a string for substitution, I 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);

    
16.10.2018 / 22:11
0

If it goes with the FireDac Queries, using the macros instead of the params In the query uses

select * from tb_teste where tb_teste.data_hora !PARAMETRO;

and in code

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

If I have a valid macro type in the query, I use mdRaw but I assume it also gives mdString

    
17.10.2018 / 13:01
0

How to load "null" parameters in Insert Query 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;

Note: Include the use DB.

    
23.11.2018 / 17:38