查询及删除重复记录的方法 (一) 1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断 select * from people whe...

一个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 ;