一个MYSQL多值查询的存储过程

 

DELIMITER $$

DROP PROCEDURE IF EXISTS `ipy`.`sp_pers_srch`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_pers_srch`(IN comp_id int(15),IN var_emplid varchar(15),
 IN var_first_name varchar(30),IN var_last_name varchar(30))
BEGIN
  SET @stmt = "SELECT emplid,first_name,last_name FROM ipy_pers_data WHERE 1 = 1";
  SET @op = "'";
  SET @e = " AND emplid = ";
  SET @f = " AND first_name = ";
  SET @l = " AND last_name = ";
  SET @c = " AND company_id = ";
  IF IFNULL(NULL,LENGTH(var_emplid)) != 'NULL' THEN
    SET @stmt = CONCAT(@stmt,@e,@op,var_emplid,@op);
  END IF;
  IF IFNULL(NULL,LENGTH(var_first_name)) != 'NULL' THEN
    SET @stmt = CONCAT(@stmt,@f,@op,var_first_name,@op);
  END IF;
  IF IFNULL(NULL,LENGTH(var_last_name)) != 'NULL' THEN
    SET @stmt = CONCAT(@stmt,@l,@op,var_last_name,@op);
  END IF;
  SET @stmt = CONCAT(@stmt,@c,@op,comp_id,@op);
  PREPARE s1 FROM @stmt;
  EXECUTE s1;
  DEALLOCATE PREPARE s1;
  IF FOUND_ROWS() != 0 THEN
    SET @error_code = 0;
  ELSE
    SET @error_code = 1;
  END IF;
  SELECT @error_code;
END$$

DELIMITER ;