BEGIN
set @a = CONCAT("shangdubbs.thread_",pid);
set @b = pid;TRUNCATE ultrax.pre_forum_thread;
PREPARE stmt FROM "
INSERT INTO ultrax.pre_forum_thread (
fid,
author,
authorid,
`subject`,
dateline,
lastpost,
lastposter,
views,
replies,
displayorder,
moderated,
digest,
closed,
heats,
`status`,
T_id,
T_parid
) SELECT
f.fid,
t.T_name AS author,
t.uid AS authorid,
t.T_title AS `subject`,
UNIX_TIMESTAMP(t.T_time) AS dateline,
UNIX_TIMESTAMP(t.T_time) AS lastpost,
t.T_lastreplyname AS lastposter,
t.T_seenum AS views,
t.T_replynum AS replies,
IF (t.T_istop = '1', 1, 0) AS displayorder,
IF (t.T_istop = '1', 1, 0) AS moderated,
IF (t.T_isdistillate = '1', 1, 0) AS digest,
IF (t.T_islock = '1', 1, 0) AS closed,
t.T_replynum AS heats,
32 AS `status`,
t.T_id,
t.T_parid
FROM
? AS t
JOIN shangdubbs.part2dz_forum AS f ON f.P_id = ?
WHERE
t.T_isdelete = '0';";
EXECUTE stmt USING @a,@b;END执行报错如下去掉存储过程 , 单独执行SQL 正常。
set @a = CONCAT("shangdubbs.thread_",pid);
set @b = pid;TRUNCATE ultrax.pre_forum_thread;
PREPARE stmt FROM "
INSERT INTO ultrax.pre_forum_thread (
fid,
author,
authorid,
`subject`,
dateline,
lastpost,
lastposter,
views,
replies,
displayorder,
moderated,
digest,
closed,
heats,
`status`,
T_id,
T_parid
) SELECT
f.fid,
t.T_name AS author,
t.uid AS authorid,
t.T_title AS `subject`,
UNIX_TIMESTAMP(t.T_time) AS dateline,
UNIX_TIMESTAMP(t.T_time) AS lastpost,
t.T_lastreplyname AS lastposter,
t.T_seenum AS views,
t.T_replynum AS replies,
IF (t.T_istop = '1', 1, 0) AS displayorder,
IF (t.T_istop = '1', 1, 0) AS moderated,
IF (t.T_isdistillate = '1', 1, 0) AS digest,
IF (t.T_islock = '1', 1, 0) AS closed,
t.T_replynum AS heats,
32 AS `status`,
t.T_id,
t.T_parid
FROM
? AS t
JOIN shangdubbs.part2dz_forum AS f ON f.P_id = ?
WHERE
t.T_isdelete = '0';";
EXECUTE stmt USING @a,@b;END执行报错如下去掉存储过程 , 单独执行SQL 正常。
#Routine body goes here...PREPARE stmt FROM 'SELECT SQRT(POW(?,2)+POW(?,2)) AS hypotenuse';
SET @a = 3;
SET @b = 4;
EXECUTE stmt USING @a,@b;
END我测试这样正常
BEGINSET @presql = CONCAT(
"INSERT INTO ultrax.pre_forum_thread (
fid,
author,
authorid,
`subject`,
dateline,
lastpost,
lastposter,
views,
replies,
displayorder,
moderated,
digest,
closed,
heats,
`status`,
T_id,
T_parid
) SELECT
f.fid,
t.T_name AS author,
t.uid AS authorid,
t.T_title AS `subject`,
UNIX_TIMESTAMP(t.T_time) AS dateline,
UNIX_TIMESTAMP(t.T_time) AS lastpost,
t.T_lastreplyname AS lastposter,
t.T_seenum AS views,
t.T_replynum AS replies,
IF (t.T_istop = '1', 1, 0) AS displayorder,
IF (t.T_istop = '1', 1, 0) AS moderated,
IF (t.T_isdistillate = '1', 1, 0) AS digest,
IF (t.T_islock = '1', 1, 0) AS closed,
t.T_replynum AS heats,
32 AS `status`,
t.T_id,
t.T_parid
FROM
shangdubbs.thread_",
pid,
" AS t
JOIN shangdubbs.part2dz_forum AS f ON f.P_id = ",
pid,
" WHERE
t.T_isdelete = '0';"
);PREPARE stmt
FROM
@presql;EXECUTE stmt;
END
这样可以
如果是通过储存传参的话,定义过程的时候储存过程带参
call sp (var1,var2)
储存过程里拼接动态sql 带变量名即可。