MySQL stored procedure with dynamic clause where

Question

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.

Someone can help me , please ?

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 <> 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$$

I guess the idea its correct, I prepare the strings to filter using %value% to use it on SQL command, and after check if the value not is null, I want to add it to WHERE clauses.

Answer 1

SQL is a declarative language. You tell the server what you want to find, rather than how to find it. The query optimizer is tasked with determining how to find the rows.

The simple solution is to let the query optimizer take care of optimizing away the unnecessary conditons, which it does automatically

WHERE
... AND
(name IS NULL OR usr.nm_user LIKE CONCAT('%',name,'%') AND
(company IS NULL OR usr.nm_company LIKE CONCAT('%',company,'%') AND
... -- repeat for other variables

In preparing the query plan, the optimizer's job is to determine how to actually find the desired rows with as little work as possible.

Since name is a variable that can't change during query execution, the optimizer resolves it to a constant. Since name is a constant, then name IS NULL is a constant expression that can be resolved to either true or false before query execution begins.

If true, the OR expression is always true, so there's no need to resolve the expression CONCAT('%',name,'%'), so this is optimized away.

If false, then the expression CONCAT('%',name,'%') is resolved to a constant and each row is compared against it. The CONCAT() doesn't need to be processed for each row, because again the value won't change from row to row, so doing this in advance isn't necessary.

So, there's no need to rewrite your query. Just formulate an expression that is logically valid, and the optimizer will do the rest.

Also, character collation is case-insensitive by default, so unless you changed this, LOWER() is unnecessary.

And, as mentioned above, your earlier blocks to concatenate the % to the variables are also unnecessary, since we can do those in WHERE only if we need to (when the variables aren't null).

Answer 2

Ok, not really seeing what your code does, but what you are most likely looking for is prepared statements: You can not concatenate a sql in a stored procedure since that is compiled code, however you can concatenate a string and place that in a prepare statement + execute that statement.

Short example:

SET @sql = CONCAT('SELECT some_columns FROM table
               WHERE a=b',  
               @your_generated_where_statement_parts);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;