在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!在网上搜索解决办法也一直未果,请知道的兄弟指点一二.
解决方案 »
- Mysql workbench 运行出错
- 多线程共用ado 连接,交替执行select 和insert时,insert语句总是报这个错误。idispatch error # 3105 Commands
- 请教,优化一个SQL语句!!!
- 5.0.77升级到5.1.47的操作步骤
- mysql占用很大内存
- 关于 MYSQL存储过程
- mysql in 与text
- 那位有实怎么现数据库方面的数据和资料?
- Specified key was too long; max key length is 1024 bytes
- 在eclipse里java连不上MySQL数据库
- 一条DELETE语句是否可以删除多行记录?
- mysql 建临时表时出现的疑惑
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未声明.