Stored procudure for a search feature I am trying to write a stored procedure to search by DB for whatever is inserted. see below:

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_all_login_tb`
(
 IN p_pass varchar(100), 
 IN p_email_id varchar(100), 
 IN p_name varchar(45), 
 IN p_staff_id varchar(45), 
 IN p_status_id varchar(1) 
)
BEGIN

    set @sqlquery = 'select * from kupo_101.login_tb where login_tb.id is NOT NULL';

    IF (p_pass is not null) THEN   
        set @sqlquery = CONCAT (@sqlquery ,' AND login_tb.pass = "',p_pass,'"');
    ELSEIF (p_email_id is not null) THEN
        set @sqlquery = CONCAT (@sqlquery ,' AND login_tb.email_id = "',p_email_id,'"');
    ELSEIF (p_name is not null) THEN
        set @sqlquery = CONCAT (@sqlquery ,' AND login_tb.first_name = "',p_name,'"');
    ELSEIF (p_staff_id is not null) THEN
        set @sqlquery = CONCAT (@sqlquery ,' AND login_tb.staff_id = "',p_staff_id,'"');
    ELSEIF (p_status_id is not null) THEN
        set @sqlquery = CONCAT (@sqlquery ,' AND login_tb.status = "',p_status_id,'"');
    END IF;

    prepare stmt from @sqlquery;
    execute stmt;

END




MariaDB [kupo_101]&gt; CALL get_all_login_tb('qewr13241wwe','','','','');
+----+--------------+------------------+----------------------+----------+--------+
| id | pass         | email_id         | first_name           | staff_id | status |
+----+--------------+------------------+----------------------+----------+--------+
|  2 | qewr13241wwe | iimama@gmail.com | Imama Igein Blessing | 10122ASD | Y      |
|  3 | qewr13241wwe | barvo@gmail.com  | ucee oluchi          | 10122AFF | N      |
+----+--------------+------------------+----------------------+----------+--------+
2 rows in set (0.00 sec)


But when use other filed to search it shows empty Set.

CALL get_all_login_tb('','','','10122ASD','');
Empty set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
It's because you're calling the stored procedure with empty string values rather than NULL values. The IFs and ELSEIFs in the SP body are testing for NULL, not empty string.