本人在存储过程中利用了PREPARE语句,但是在CALL这个时候报错,请高手们看看是那个地方有问题,创建过程的时候没报错。
具体代码:
-- 原本是这样的查询语句
SELECT t1.bankprovincecode,t1.bankprovincename,"2" AS area_level,COUNT(t3.corpid)AS c_corpid
FROM bankbranchparams AS t1
INNER JOIN corpinfo AS t3 ON t1.bankbranchleadercode=t3.bankbranchleadercode
WHERE t3.applydate <= "20160325" AND t3.version<>"entjxcstd"
GROUP BY t1.bankprovincecode,t1.bankprovincename;
-- 创建过程
DROP PROCEDURE IF EXISTS proc_test;
CREATE PROCEDURE proc_test
(v_cname VARCHAR(20),
v_cname2 VARCHAR(20),
v_nextlevel VARCHAR(10),
i_date VARCHAR(20))
BEGIN
SET @sqlstr1=CONCAT("SELECT t1.",v_cname,"t1.",v_cname2,v_nextlevel,
"AS area_level,COUNT(t3.corpid)AS c_corpid FROM bankbranchparams AS t1
INNER JOIN corpinfo AS t3 ON t1.bankbranchleadercode=t3.bankbranchleadercode
WHERE t3.applydate <=",i_date, "AND t3.version<>","entjxcstd",
"GROUP BY t1.",v_cname,"t1.",v_cname2);
PREPARE stmt1 FROM @sqlstr1;
EXECUTE stmt1;
END;
CALL proc_test("bankprovincecode","bankprovincecodename","2","20160325");
报错:
[SQL]CALL proc_test(“bankprovincecode”,”bankprovincecodename”,”2″,”20160325″);
[Err] 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 “t3.version<>entjxcstdGROUP BY t1.bankprovincecodet1.bankprovincecodename” at line 3