MYSQL 调用存储过程时出错

Question

我正在尝试创建一个存储过程，但在调用时出现以下错误。

存储过程如下

CREATE DEFINER=`root`@`%` PROCEDURE `CreateTableProjectIssues`(IN projName     VARCHAR(200), IN tablName VARCHAR(200))
BEGIN
SET @comnd = CONCAT('create table ',tablName,' as ( select r1.key, s1.comments, p1.fname, s1.line, s1.status 
 FROM test.issues s1, test.proj p1, test.rules r1 
 WHERE s1.root_component_id=(select id from test.proj p2 
                                   where p2.name=''',projName,''') 
       and s1.component_id=p1.id 
       and s1.rule_id=r1.id');
 PREPARE stmt FROM @comnd;
 EXECUTE stmt;
END

并出现以下错误

Error Code: 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 '' at line 5

注意:我知道 SQL 查询格式错误，未使用连接。

知道为什么会出现这个错误吗？

Answer 1

你的(太多了。试试这个:

CREATE DEFINER=`root`@`%` PROCEDURE `CreateTableProjectIssues`(IN projName     VARCHAR(200), IN tablName VARCHAR(200))
BEGIN
SET @comnd = CONCAT('create table ',tablName,' as select r1.key, s1.comments, 

/*removed the (                             here ^  */

p1.fname, s1.line, s1.status 
 FROM test.issues s1, test.proj p1, test.rules r1 
 WHERE s1.root_component_id=(select id from test.proj p2 
                                   where p2.name=''',projName,''') 
       and s1.component_id=p1.id 
       and s1.rule_id=r1.id');
 PREPARE stmt FROM @comnd;
 EXECUTE stmt;
END