在mysql的存储过程中是否可以使用PREPARE来动态执行SQL语句进行数据表数据的插入.如下存储过程.其中表a,只有两个字段 一个int的自动增长类型,一个varchar(200).
CREATE PROCEDURE p9(in name varchar(900))
BEGINDECLARE c varchar(100);
-- INSERT INTO a (_name) VALUES (name);set c = concat('INSERT INTO a (_name) VALUES (');
select c;set c=concat(c,name);
select c;set c=concat(c,')');select c;
PREPARE stmt from @sqlstr;
EXECUTE sqlstr;
commit;
END运行后数据库报错如下:
ERROR 1064 (42000): 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 1mysql数据库版本为:
mysql> SELECT VERSION();
+---------------------+
| VERSION() |
+---------------------+
| 5.0.67-community-nt |
+---------------------+
环境为windows XP!在网上搜索解决办法也一直未果,请知道的兄弟指点一二.
CREATE PROCEDURE p9(in name varchar(900))
BEGINDECLARE c varchar(100);
-- INSERT INTO a (_name) VALUES (name);set c = concat('INSERT INTO a (_name) VALUES (');
select c;set c=concat(c,name);
select c;set c=concat(c,')');select c;
PREPARE stmt from @sqlstr;
EXECUTE sqlstr;
commit;
END运行后数据库报错如下:
ERROR 1064 (42000): 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 1mysql数据库版本为:
mysql> SELECT VERSION();
+---------------------+
| VERSION() |
+---------------------+
| 5.0.67-community-nt |
+---------------------+
环境为windows XP!在网上搜索解决办法也一直未果,请知道的兄弟指点一二.
2. prepare / execute 的语法CREATE PROCEDURE p9(in name varchar(900))
BEGIN DECLARE c varchar(100);
set c = concat('INSERT INTO a (_name) VALUES (\'',name,'\')');
set @sqlstr=c;
PREPARE stmt from @sqlstr;
EXECUTE stmt;
commit;
END
我是看5.0的呀 还5.1的呀 版本也太多了吧
基本解决,但上面SQL code中strsql未声明.