title: MySQL stored procedure with dynamic clause where
body: <p>I need to construct a SQL clause dynamic, I saw some examples using only case when, but for some reason my source code does not work.</p>
  
  <p>Someone can help me , please ?</p>
  
  <pre><code>create procedure sp_test(in iduser bigint, in name varchar(50), in company varchar(50), in city varchar(50), in profession varchar(50))
  begin
      if not(name is null) then 
          begin   
              set name = '%' + lower(name) + '%'; 
          end;
      end if;
  
      if not(company is null) then 
          begin   
              set company = '%' + lower(company) + '%';
          end;
      end if;
  
      if not(city is null) then 
          begin   
              set city = '%' + lower(city) + '%'; 
          end;
      end if;
  
      if not(profession is null) then 
          begin   
              set profession = '%' + lower(profession) + '%';
          end;
      end if;
  
      select 
              usr.id_user,
              usr.ds_icon,
              usr.nm_user,
              usr.ds_slug,
              usr.ds_title,
              usr.nm_company
          from 
              tbl_user usr
              left join tbl_profession pro on (pro.id_profession = usr.id_profession)
              left join tbl_resume res on (res.id_user = usr.id_user)
          where
              (usr.ds_activation is null) and
              usr.id_user &lt;&gt; iduser and
              usr.id_user not in (select id_friend from tbl_user_friend where id_user = iduser) and
              usr.id_user not in (select id_user from tbl_user_friend where id_friend = iduser) and
              usr.id_user not in (select id_friend from tbl_user_friend_not_suggest where id_user = iduser) and
              case when not(name is null) then 
                  lower (usr.nm_user) like lower(name) or 
              end
              case when not(company is null) then 
                  lower (usr.nm_company) like lower(company) or 
              end
              case when not(profession is null) then 
                  lower (pro.nm_profession) like lower(profession) or 
              end
              case when not(city is null) then 
                  lower (res.ds_city) like lower(city) or 
              end
              1 = 1
          order by
              usr.nm_user
          limit
              0,20
          ;
  end$$
  </code></pre>
  
  <p>I guess the idea its correct, I prepare the strings to filter using <code>%value%</code> to use it on SQL command, and after check if the value not is null, I want to add it to WHERE clauses.</p>