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

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

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 ;

相关文章：

你感兴趣的文章：

标签云：