MYSQL 的多值查询示例

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