Ankita Kumari

Ankita Kumari

  • 2k
  • 41
  • 1.6k

mysql stored procedure

Dec 1 2021 10:59 AM

CREATE
    
    PROCEDURE `insuertika_panel1`.`search_candidate`( var_jtitle VARCHAR(50),var_city VARCHAR(50),var_rtitle VARCHAR(50),var_rzip VARCHAR(50),var_state VARCHAR(50),var_exp INT(11))
    
    BEGIN
    
    DECLARE jtitle VARCHAR(50);
    DECLARE city VARCHAR(50);
     DECLARE rtitle VARCHAR(50);
    DECLARE rzip VARCHAR(50);
    DECLARE r_state VARCHAR(50);
    DECLARE r_exp INT(11);
    
    SET @jtitle= var_jtitle;
    SET @city=var_city;
    SET @rtitle=var_rtitle;
    SET @rzip=var_rzip;
    SET @r_state=var_state;
    SET @r_exp=var_exp;
    
    SELECT * FROM tbl_candidate WHERE (`job_title`  IS NOT NULL OR `job_title` LIKE CONCAT('%',@jtitle,'%')) OR
     ( `candidate_city` IS NOT  NULL OR `candidate_city` LIKE CONCAT('%',@city,'%'))
      OR (`resume_title`  IS NOT  NULL OR `resume_title` LIKE CONCAT('%',@rtitle,'%') )
     OR (`Zip` IS NOT NULL OR `Zip` LIKE  CONCAT('%',@rzip,'%') ) OR (`candidate_state` IS NOT NULL OR `candidate_state` LIKE CONCAT('%',  @r_state,  '%')) OR
     (`exp` IS NOT NULL OR `exp` LIKE CONCAT('%' ,@r_exp, '%')) LIMIT 100;
    END$$

DELIMITER ;

why above code is not working ? if we call procedure like call search_candidate('soft','','','','','') then it returns the other job_title other than software. pls give me full fledge solution for dynamic search.


Answers (1)