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不能同时使用,有什么办法解决!
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不能同时使用,有什么办法解决!
解决方案 »
- 求SQL文怎么写
- MYSQL存储过程实现多表插入
- 存储过程中创建临时表,其中表名是参数,如何实现?
- linux 下用KDevelop编写C语言操作mysql数据库的程序, 如何将mysql的库文件( *.a)加入到编译器中进行编译.
- ▲△▲在程序中怎么导入sql脚本文件?
- 请问在postgres中,我如何创建索引?注意如果存在索引就得同事删除这个索引再建
- 求指点 合并语句
- 1405 Access denied for user '[email protected]' 问题
- Mysql本地数据库1130错误
- golang里面database/sql限制只能返回一组行集,mysql的存储过程一次会返回多组行集,怎么解决啊?
- 使用rand()作为查询条件出现的奇怪的问题
- mysql查询问题
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实现
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 ;