Question

mysql 建立传表名的参数，存储过程没出错，但是调用的时候显示错误如下

[SQL] call PRO_NCOMPCODE('sw_dt_sk_basicinfo','sw_basic_new_company_id');
[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 'NULL' at line 1
CREATE DEFINER = `root`@`localhost` PROCEDURE `NewProc`(IN TAB_NCODE VARCHAR(50),IN TAB_COMID VARCHAR(50))
BEGIN
start TRANSACTION;
set @sqlcm:=concat("UPDATE ",TAB_NCODE," SET ",TAB_NCODE,".NCOMPCODE=",TAB_COMID,".new_comp_id "
" where ",TAB_NCODE,".SYMBOL=",TAB_COMID,".SYMBOL"
" and (",TAB_NCODE,".NCOMPCODE is NULL OR LENGTH(",TAB_NCODE,".NCOMPCODE)=0)");
prepare stmt from @sqlcmd;
execute stmt;
COMMIT;
end;

Answer 1

把sqlcm的值改下
set  @sqlcm:=concat("UPDATE ",TAB_NCODE,",",TAB_COMID ," SET ",TAB_NCODE,".NCOMPCODE=",TAB_COMID,".new_comp_id "
" where ",TAB_NCODE,".SYMBOL=",TAB_COMID,".SYMBOL"
"  and (",TAB_NCODE,".NCOMPCODE is NULL OR LENGTH(",TAB_NCODE,".NCOMPCODE)=0)");

做拼接在调试阶段建议在set @sqlcm之后,加上:

SELECT @sqlcm;

这样在call时就可以看到中间结果了.