我创建了以下存储过程以将数据插入数据库:
CREATE DEFINER=`PotatoHead`@`%` PROCEDURE `InsertIntoTable`(in
tablename varchar(45),in ID varchar(45),in Project
varchar(45),in Variant varchar(45),
in ReleaseVersion varchar(45) ,
in TestBenchID varchar(45) ,
in TestCaseID int(11) ,
in TestCaseNamespace varchar(100) ,
in TestCaseName varchar(45),
in TestCaseDomain varchar(45) ,
in TestType varchar(45) ,
in HardwareVersion varchar(4000) ,
in SoftwareVersion varchar(4000),
in Result varchar(45) ,
in Comment varchar(4000),
in Duration varchar(45) ,
in StartTime varchar(45) ,
in EndTime varchar(45) )
BEGIN
SET @sql = CONCAT('Insert into ', tablename );
SET @sql = CONCAT('ID,Project,Variant,');
SET @sql = CONCAT('ReleaseVersion,');
SET @sql = CONCAT('TestBenchID ,');
SET @sql = CONCAT('TestCaseID ,');
SET @sql = CONCAT('TestCaseNamespace ,');
SET @sql = CONCAT('TestCaseName ,');
SET @sql = CONCAT('TestCaseDomain ,');
SET @sql = CONCAT('TestType ,');
SET @sql = CONCAT('HardwareVersion ,');
SET @sql = CONCAT('SoftwareVersion ,');
SET @sql = CONCAT('Result ,');
SET @sql = CONCAT('Comment ,');
SET @sql = CONCAT('Duration ,');
SET @sql = CONCAT('StartTime ,');
SET @sql = CONCAT('EndTime) VALUES (');
SET @sql = CONCAT(ID,',',Project,',',Variant,',',
ReleaseVersion,',',
TestBenchID,',',
TestCaseID,',',
TestCaseNamespace ,',',
TestCaseName ,',',
TestCaseDomain ,',',
TestType ,',',
HardwareVersion ,',',
SoftwareVersion ,',',
Result ,',',
Comment ,',',
Duration ,',',
StartTime ,',',
EndTime,');');
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;
END
当我尝试用以下查询调用此函数时:
Call InsertIntoTable('oablp1992','id','project','variant',
'releaseversion',
'testbenchid',
1,
'testcasenamespace',
'testcasename',
'Domain',
'type',
'hardware',
'software',
'result',
'comment',
'Duration',
'starttime',
'endtime');
Insert into oablp1992(ID,Project,Variant,
ReleaseVersion,
TestBenchID ,
TestCaseID ,
TestCaseNamespace ,
TestCaseName ,
TestCaseDomain ,
TestType ,
HardwareVersion ,
SoftwareVersion ,
Result ,
Comment ,
Duration ,
StartTime ,
EndTime) VALUES('id','project','variant',
'releaseversion',
'testbenchid',
'1',
'testcasenamespace',
'testcasename',
'Domain',
'type',
'hardware',
'software',
'result',
'comment',
'Duration',
'starttime',
'endtime');
我收到以下错误:
错误代码:1064。sql语法有错误;请查看与您的mysql服务器版本对应的手册,在第1行的“id,project,variant,releaseversion,testbenchid,1,testcasenamespace,testcasename,d”附近找到正确的语法
如何解决这个问题?
1条答案
按热度按时间7uhlpewt1#
如果您使用一个命名约定,将参数和列名清楚地区分开来,那么就更清楚了。但也许这就是你想要的。注意ascii字符39(单引号)的使用。
它生成以下sql语句
当您希望将其传递给sql时,不要忘记删除注解掉的行。