Is there an easier way to do multiple nested IFs in mysql?

Question

I am trying to write a stored procedure in mysql that has 10 'in' parameters.

The problem is that some parameters might be null or have a value. In case of null it shouldn't try to look for that column in the where part. Then there is the same for column sorting.

The way I have it currently looks like this:

if(par1 is null) then
  if(par2 is null) then
    if(par3 is null) then
      -- some query: select * from blabla where 1=1 order by something
    else
      -- other query: select * from blabla where col3=par3 order by something
  else
    if(par3 is null) then
      -- some query: select * from blabla where col2=par2 order by something
    else
      -- other query: select * from blabla where col2=par2 and col3=par3 order by something
else
  -- same as in the 'if' part

So as you can see, it gets tedious to work with very quickly, now imagine this being done for many parameters. How can I simplify this, if possible? Any advice?

Answer 1

You could write a single query that takes all the parameter at once and uses coalesce() to ignore null values:

select * 
from blabla 
where 
        col1 = coalesce(@par1, col1)
    and col2 = coalesce(@par2, col2)
    and col3 = coalesce(@par3, col3)
order by something

You also spell this as the longer, but maybe clearer:

select * 
from blabla 
where 
        (@par1 is null or col1 = @par1)
    and (@par2 is null or col2 = @par2)
    and (@par3 is null or col3 = @par3)
order by something