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?
You could write a single query that takes all the parameter at once and uses
coalesce()
to ignorenull
values:You also spell this as the longer, but maybe clearer: