我写的一个例子。
DELIMITER $$DROP PROCEDURE IF EXISTS `ipy`.`sp_message_multi_search`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_message_multi_search`(IN var_seq_condition varchar(1),
IN var_f_date_condition varchar(1),IN var_e_date_condition varchar(1),
IN var_seq int(11),IN var_f_date date,IN var_e_date date)
BEGIN
SET @stmt = "SELECT * FROM ipy_message WHERE 1 = 1";
SET @op = "'";
SET @a = CONCAT(" AND seq_number ",var_seq_condition," ");
SET @b = CONCAT(" AND from_date ",var_f_date_condition," ");
SET @c = CONCAT(" AND end_date ",var_e_date_condition," ");
IF IFNULL(NULL,LENGTH(var_seq)) != 'NULL' && STRCMP(var_seq,'0') != 0 THEN
SET @stmt = CONCAT(@stmt,@a,var_seq);
END IF;
IF IFNULL(NULL,LENGTH(var_f_date)) != 'NULL' && STRCMP(var_f_date,'0000-00-00') != 0 THEN
SET @stmt = CONCAT(@stmt,@b,@op,var_f_date,@op);
END IF;
IF IFNULL(NULL,LENGTH(var_e_date)) != 'NULL' && STRCMP(var_e_date,'0000-00-00') != 0 THEN
SET @stmt = CONCAT(@stmt,@c,@op,var_e_date,@op);
END IF;
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 ;
DELIMITER $$DROP PROCEDURE IF EXISTS `ipy`.`sp_message_multi_search`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_message_multi_search`(IN var_seq_condition varchar(1),
IN var_f_date_condition varchar(1),IN var_e_date_condition varchar(1),
IN var_seq int(11),IN var_f_date date,IN var_e_date date)
BEGIN
SET @stmt = "SELECT * FROM ipy_message WHERE 1 = 1";
SET @op = "'";
SET @a = CONCAT(" AND seq_number ",var_seq_condition," ");
SET @b = CONCAT(" AND from_date ",var_f_date_condition," ");
SET @c = CONCAT(" AND end_date ",var_e_date_condition," ");
IF IFNULL(NULL,LENGTH(var_seq)) != 'NULL' && STRCMP(var_seq,'0') != 0 THEN
SET @stmt = CONCAT(@stmt,@a,var_seq);
END IF;
IF IFNULL(NULL,LENGTH(var_f_date)) != 'NULL' && STRCMP(var_f_date,'0000-00-00') != 0 THEN
SET @stmt = CONCAT(@stmt,@b,@op,var_f_date,@op);
END IF;
IF IFNULL(NULL,LENGTH(var_e_date)) != 'NULL' && STRCMP(var_e_date,'0000-00-00') != 0 THEN
SET @stmt = CONCAT(@stmt,@c,@op,var_e_date,@op);
END IF;
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 ;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货