mysql dynamic sql

Question

I have a question about the following code which I'm using in a prepare-execute stored procedure:

if(param_cod_empresa_pad='') then
set @param_cod_empresa_pad=true;
set @condicion18='and ? ';
elseif(param_cod_empresa_pad='0') then
set @param_cod_empresa_pad=null;
set @condicion18='and pc.cod_empresa_pad is ? ';
else
set @param_cod_empresa_pad=param_cod_empresa_pad;
set @condicion18='and pc.cod_empresa_pad=? ';
end if;

The second condition when param_cod_empresa_pad='0' doesn't work, I have to write it this way:

set @param_cod_empresa_pad=true;
set @condicion18='and pc.cod_empresa_pad is null and ? ';

Does anybody know why? I would like to know if there is a better way to call a null variable using dynamic sql in mysql.

I also tried:

set @param_cod_empresa_pad='null';
set @condicion18='and pc.cod_empresa_pad is ? ';

but didn't work.

Answer 1

You can not use is with parameters.
Alternatively you can use the <=> operator which is a = that returns true when comparing null values.

null = null   -> false
null <=> null -> true
1 <=> 1       -> true
1 <=> 2       -> false

elseif(param_cod_empresa_pad='0') then
set @param_cod_empresa_pad=null;
set @condicion18='and pc.cod_empresa_pad <=> ? ';