DROP PROCEDURE IF EXISTS SP_ImportTestCases;CREATE PROCEDURE `SP_ImportTestCases`( TestProjectID INT ,TestPlanID INT ) BEGIN SET @a = 'select * from test'; PREPARE sqlstr from @a; EXECUTE sqlstr; DEALLOCATE PREPARE sqlstr; END;
上面那个可以,这个不可以,两个一样的吧,总是说[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 'CreateTableStmt; DROP PROCEDURE IF EXISTS SP_ImportTestCases;CREATE PROCEDURE SP_ImportTestCases( TestProjectID INT ,TestPlanID INT ) BEGIN
#create temporary table SET @TemporaryTableName = CONCAT('TempTable', DATE_FORMAT(SYSDATE(), '%Y%m%d%H%i%S'), REPLACE(CAST(RAND() AS CHAR(10)), '.', '')); SET @CreateTableStmt = CONCAT('CREATE TEMPORARY TABLE ', @TemporaryTableName); SELECT CONCAT(@CreateTableStmt, ' (`TestCaseName` VARCHAR(100) NOT NULL, `Summary` VARCHAR(500), `Steps` VARCHAR(1000), `Results` VARCHAR(500),' , GROUP_CONCAT('`', `name`, '`', ' VARCHAR(500)') , ') TYPE=HEAP') INTO @CreateTableStmt FROM `custom_fields` A JOIN `cfield_testprojects` B ON A.id = B.field_id WHERE B.testproject_id = TestProjectID; PREPARE CreateTableStmt FROM @CreateTableStmt; EXECUTE CreateTableStmt; DEALLOCATE CreateTableStmt;
END
CREATE PROCEDURE `SP_ImportTestCases`(TestProjectID INT,TestPlanID INT) BEGIN SET @a = 'select * from test'; PREPARE sqlstr from @a; EXECUTE sqlstr; DEALLOCATE PREPARE sqlstr; END;我这里没错
在命令行下输入? DELIMITER $$ DROP PROCEDURE IF EXISTS SP_ImportTestCases$$CREATE PROCEDURE `SP_ImportTestCases`(TestProjectID INT,TestPlanID INT) BEGIN SET @a = 'select * from test'; PREPARE sqlstr FROM @a; EXECUTE sqlstr; DEALLOCATE PREPARE sqlstr; END$$ DELIMITER ;
这样也是错的DROP PROCEDURE IF EXISTS SP_ImportTestCases; delimiter $$ CREATE PROCEDURE SP_ImportTestCases( TestProjectID INT ,TestPlanID INT ) BEGIN
#create temporary table SET @TemporaryTableName = CONCAT('TempTable', DATE_FORMAT(SYSDATE(), '%Y%m%d%H%i%S'), REPLACE(CAST(RAND() AS CHAR(10)), '.', '')); SET @CreateTableStmt = CONCAT('CREATE TEMPORARY TABLE ', @TemporaryTableName); SELECT CONCAT(@CreateTableStmt, ' (`TestCaseName` VARCHAR(100) NOT NULL, `Summary` VARCHAR(500), `Steps` VARCHAR(1000), `Results` VARCHAR(500),' , GROUP_CONCAT('`', `name`, '`', ' VARCHAR(500)') , ') TYPE=HEAP') INTO @CreateTableStmt FROM `custom_fields` A JOIN `cfield_testprojects` B ON A.id = B.field_id WHERE B.testproject_id = TestProjectID; PREPARE CreateTableStmt FROM @CreateTableStmt; EXECUTE CreateTableStmt; DEALLOCATE CreateTableStmt;
END$$delimiter;
DELIMITER $$ DROP PROCEDURE IF EXISTS SP_ImportTestCases$$CREATE PROCEDURE SP_ImportTestCases(TestProjectID INT,TestPlanID INT) BEGIN -- create temporary table SET @TemporaryTableName = CONCAT('TempTable', DATE_FORMAT(SYSDATE(), '%Y%m%d%H%i%S'), REPLACE(CAST(RAND() AS CHAR(10)), '.', '')); SET @CreateTableStmt = CONCAT('CREATE TEMPORARY TABLE ', @TemporaryTableName); SELECT CONCAT(@CreateTableStmt,'(`TestCaseName` VARCHAR(100) NOT NULL,`Summary` VARCHAR(500),`Steps` VARCHAR(1000),`Results` VARCHAR(500)',GROUP_CONCAT('`', `name`, '`', ' VARCHAR(500)'),') TYPE=HEAP') INTO @CreateTableStmt FROM `custom_fields` A JOIN `cfield_testprojects` B ON A.id = B.field_id WHERE B.testproject_id = TestProjectID; PREPARE CreateTableStmt FROM @CreateTableStmt; EXECUTE CreateTableStmt; DEALLOCATE PREPARE CreateTableStmt; END$$ DELIMITER ;
DROP PROCEDURE IF EXISTS SP_ImportTestCases;CREATE PROCEDURE SP_ImportTestCases(
TestProjectID INT
,TestPlanID INT
)
BEGIN
#create temporary table
SET @TemporaryTableName = CONCAT('TempTable', DATE_FORMAT(SYSDATE(), '%Y%m%d%H%i%S'), REPLACE(CAST(RAND() AS CHAR(10)), '.', ''));
SET @CreateTableStmt = CONCAT('CREATE TEMPORARY TABLE ', @TemporaryTableName); SELECT CONCAT(@CreateTableStmt, ' (`TestCaseName` VARCHAR(100) NOT NULL,
`Summary` VARCHAR(500),
`Steps` VARCHAR(1000),
`Results` VARCHAR(500),'
, GROUP_CONCAT('`', `name`, '`', ' VARCHAR(500)')
, ') TYPE=HEAP') INTO @CreateTableStmt
FROM `custom_fields` A JOIN `cfield_testprojects` B ON A.id = B.field_id
WHERE B.testproject_id = TestProjectID; PREPARE CreateTableStmt FROM @CreateTableStmt; EXECUTE CreateTableStmt; DEALLOCATE CreateTableStmt;
END
BEGIN
SET @a = 'select * from test';
PREPARE sqlstr from @a;
EXECUTE sqlstr;
DEALLOCATE PREPARE sqlstr;
END;我这里没错
DELIMITER $$
DROP PROCEDURE IF EXISTS SP_ImportTestCases$$CREATE PROCEDURE `SP_ImportTestCases`(TestProjectID INT,TestPlanID INT)
BEGIN
SET @a = 'select * from test';
PREPARE sqlstr FROM @a;
EXECUTE sqlstr;
DEALLOCATE PREPARE sqlstr;
END$$
DELIMITER ;
delimiter $$
CREATE PROCEDURE SP_ImportTestCases(
TestProjectID INT
,TestPlanID INT
)
BEGIN
#create temporary table
SET @TemporaryTableName = CONCAT('TempTable', DATE_FORMAT(SYSDATE(), '%Y%m%d%H%i%S'), REPLACE(CAST(RAND() AS CHAR(10)), '.', ''));
SET @CreateTableStmt = CONCAT('CREATE TEMPORARY TABLE ', @TemporaryTableName); SELECT CONCAT(@CreateTableStmt, ' (`TestCaseName` VARCHAR(100) NOT NULL,
`Summary` VARCHAR(500),
`Steps` VARCHAR(1000),
`Results` VARCHAR(500),'
, GROUP_CONCAT('`', `name`, '`', ' VARCHAR(500)')
, ') TYPE=HEAP') INTO @CreateTableStmt
FROM `custom_fields` A JOIN `cfield_testprojects` B ON A.id = B.field_id
WHERE B.testproject_id = TestProjectID; PREPARE CreateTableStmt FROM @CreateTableStmt; EXECUTE CreateTableStmt; DEALLOCATE CreateTableStmt;
END$$delimiter;
DROP PROCEDURE IF EXISTS SP_ImportTestCases$$CREATE PROCEDURE SP_ImportTestCases(TestProjectID INT,TestPlanID INT)
BEGIN
-- create temporary table
SET @TemporaryTableName = CONCAT('TempTable', DATE_FORMAT(SYSDATE(), '%Y%m%d%H%i%S'), REPLACE(CAST(RAND() AS CHAR(10)), '.', ''));
SET @CreateTableStmt = CONCAT('CREATE TEMPORARY TABLE ', @TemporaryTableName);
SELECT CONCAT(@CreateTableStmt,'(`TestCaseName` VARCHAR(100) NOT NULL,`Summary` VARCHAR(500),`Steps` VARCHAR(1000),`Results` VARCHAR(500)',GROUP_CONCAT('`', `name`, '`', ' VARCHAR(500)'),') TYPE=HEAP') INTO @CreateTableStmt
FROM `custom_fields` A JOIN `cfield_testprojects` B ON A.id = B.field_id WHERE B.testproject_id = TestProjectID;
PREPARE CreateTableStmt FROM @CreateTableStmt;
EXECUTE CreateTableStmt;
DEALLOCATE PREPARE CreateTableStmt;
END$$
DELIMITER ;