查询及删除重复记录的方法 (一) 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 ;
页:
[1]