这个SP写的不是很规范，有时间我修改一下。
-- Created by david yeung 2006-10-31
-- A store procedure to get result from mutiple offered values
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`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
-- The original sql statments.
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 = ";
-- Begin if.
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;
-- End if.
-- Concat all the dynamic sql statement.
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;
-- Free memory.
SET @stmt = '';
SET @c = '';
SET @e = '';
SET @l = '';
SET @f = '';
END$$
DELIMITER ;
本文转自 david_yeung 51CTO博客，原文链接：http://blog.51cto.com/yueliangdao0608/81403，如需转载请自行联系原作者
