Is there a way to adapt an SQL statement when a null field arrives?

Question

Is there a way to adapt an SQL statement when a null field arrives?

Navigation

#1 by (2 votes)
#2 by (1 votes)

2

Hi, I hope you're ok, is there any way to adapt an SQL statement so that it reacts according to whether a parameter arrives nil or not, something like this:

drop table if exists tabla;

create table tabla (
   id int primary key AUTO_INCREMENT, 
   fecha date not null
);


insert into tabla (fecha) values (STR_TO_DATE('24-May-2018', '%d-%M-%Y'));

SET @fecha_radicado = STR_TO_DATE('24-May-2018', '%d-%M-%Y');
SELECT  @fecha_radicado; 
SELECT * 
from tabla as  t
where  (
          CASE @fecha_radicado
            WHEN IS NOT NULL THEN  t.fecha = @fecha_radicado
            WHEN IS NULL THEN t.fecha  is NULL
          END;
       ) as fe;

Note that the query has the CASE, takes an action depending on the parameter that arrives, is there any operator that allows to perform this type of conditions within the CRUD statements when it occurs that one does not know if the parameter is null or has something? Yes, there are solutions like these:

make a judgment for each case, which would be completely inefficient because if 20 parameters arrive, it would be almost (20 C 2) approximately 190 sentences that it would have to carry out.
Make a method already in a language like php, c # or whatever it is that allows me to create a sentence that suits the need and then it is executed.

But I appreciate if anyone knows if something like that can be done within a select. THANK YOU!!

NOTE: I understand that there are mutiles SQL engines, but as I understand there is the ANSI scheme, so I put the question open, however I would be more interested if it is for MySQL.

To test: rextester.com/JZTO58225

mysql sql php oracle postgresql

asked by Johan Inglorion 21.08.2018 в 18:07

source

2 answers

Script in object c # Format for currency (MXN) DataFormatString

score 2 · Answer 1

This answer does not apply in all cases, but I usually do something like this to use parameters of the type one or all where, in the parameter, the special value null means all records .

For this I use the coalesce function, which returns the first non-zero value of its parameter list.

For example, coalesce(null, null, null, 1, 2, 3) , will return the value 1 .

So, then, an example with 2 parameters would look more or less like this:

declare @ParamInt int = null;
declare @ParamFecha date = '20180822';

select   t.campo1
       , t.campo2
       , coalesce(@ParamInt, t.CampoInt) CampoInt
       , coalesce(@ParamFecha, t.CampoFecha) CampoFecha
  from MiTabla t
 where t.CampoInt = coalesce(@ParamInt, t.CampoInt)
   and t.CampoFecha = coalesce(@ParamFecha, t.CampoFecha);

The main disadvantage of this is in the performance, because the engine will be unable to produce an optimal plan for each case. This usually does not have major implications if the query processes a few hundred or thousands of records in an environment with few users, because it represents an imperceptible delay for an average user.

But if the query should process important magnitudes of information or is part of a system with high concurrency, then I usually exchange ease in the programming for a better performance and, although it sounds tedious, it might be better to write those 200 different queries, or a class that is capable of producing an ad-hoc consultation for each case, because in this way the engine will be able to choose the best execution plan for each particular need.

score 1 · Answer 2

In mysql you could use the case when function and the syntax would be the following:

 select *,(CASE 
          WHEN @fecha_radicado IS NOT NULL THEN @fecha_radicado
          WHEN @fecha_radicado IS NULL     THEN null
         END) as fecha_radicado
 from tabla t

When the date parameter is not null, it will show you the data of t.fecha

When the date parameter is null, it will show you Null .

edited 1 as seen in your query, would edit your select in the following way:

 select * 
 from tabla t
 where t.fecha =  
 CASE 
    WHEN @fecha_radicado IS NOT NULL THEN @fecha_radicado
    WHEN @fecha_radicado IS NULL     THEN null
 END