谁帮小弟我看看这个有关问题

哪位高手帮我看看这个问题!

SQL code

CREATE PROCEDURE sp_org_search(morg_name VARCHAR(32),mstart_date date, mend_date date)BEGIN    IF(mstart_date IS NOT NULL & mend_date IS NOT NULL ) THEN        SELECT * FROM t_org WHERE org_name LIKE CONCAT('%',morg_name,'%') AND closing_date BETWEEN mstart_date AND mend_date   ;    ELSEIF(mstart_date IS NOT NULL)        SELECT * FROM t_org WHERE org_name LIKE CONCAT('%',morg_name,'%') AND closing_date >= mstart_date ;    ELSEIF(mend_date IS NOT NULL)        SELECT * FROM t_org WHERE org_name LIKE CONCAT('%',morg_name,'%') AND closing_date <= mend_date ;    IF END;END;

报错:1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '& mend_date IS NOT NULL ) THEN
SELECT * FROM t_org WHERE org_name LIKE CONCAT' at line 3
是不是like和BETWEEN不能同时使用,有什么办法解决!

[解决办法]
语法错误真多
CREATE PROCEDURE sp_org_search(morg_name VARCHAR(32),mstart_date date, mend_date date)
BEGIN
IF(mstart_date IS NOT NULL & mend_date IS NOT NULL ) THEN
SELECT * FROM t_org BETWEEN mstart_date AND mend_date;
ELSEIF(mstart_date IS NOT NULL) then
SELECT * FROM t_org ;
ELSEIF(mend_date IS NOT NULL) then 
SELECT * FROM t_org ;
end if;
END;

另外你的这种like 需要拼接动态sql实现

[解决办法]
DELIMITER $$
CREATE PROCEDURE sp_org_search(morg_name VARCHAR(32),mstart_date DATE, mend_date DATE)
BEGIN
IF (mstart_date IS NOT NULL AND mend_date IS NOT NULL ) THEN
SELECT * FROM t_org WHERE org_name LIKE CONCAT('%',morg_name,'%') AND closing_date BETWEEN mstart_date AND mend_date;
ELSEIF mstart_date IS NOT NULL THEN 
SELECT * FROM t_org WHERE org_name LIKE CONCAT('%',morg_name,'%') AND closing_date >= mstart_date; 
ELSEIF (mend_date IS NOT NULL) THEN
SELECT * FROM t_org WHERE org_name LIKE CONCAT('%',morg_name,'%') AND closing_date <= mend_date;
END IF ;
END$$
DELIMITER ;

查看更多 下一篇