1、correct:
delimiter $$
create procedure getJointProjectIDByStudentIDAndProjStatus
(IN StuID varchar(16), IN ProjStatus int, IN fromPara VARCHAR(4), IN toPara VARCHAR(4))
BEGIN
SET @dd = CONCAT(
'select project.projid from stuandproj,project where project.projid = stuandproj.projid and stuandproj.stuid = StuID and project.projstatus = ',
ProjStatus,
' and stuandproj.accepted=1 LIMIT ',
fromPara,
',',
toPara );
PREPARE stmt FROM @dd;
EXECUTE stmt;
END$$
delimiter ;
2、error:
delimiter $$
create procedure getJointProjectIDByStudentIDAndProjStatus
(IN StuID varchar(16), IN ProjStatus int, IN fromPara VARCHAR(4), IN toPara VARCHAR(4))
BEGIN
SET @dd = CONCAT(
'select project.projid from stuandproj,project where project.projid = stuandproj.projid and stuandproj.stuid = StuID and project.projstatus = ProjStatus and stuandproj.accepted=1 LIMIT ',
fromPara,
', ',
toPara );
PREPARE stmt FROM @dd;
EXECUTE stmt;
END$$
delimiter ;
---------------------------------------------------------------------------------------------------------------------------------------
如果参数ProjStatus放在整个CONCAT函数的第一个参数中的话,会出现错误,这个参数在SP真正执行的时候会被忽略掉。
问题解决:将ProjStatus做为单独的CONCAT函数的参数传进去,即如上的,正确的方式是传给CONCAT六个参数而不是四个。