MySQL Forums
Forum List  »  Stored Procedures

Dynamic SQL is not allowed in stored function or trigger
Posted by: luo song
Date: July 29, 2020 09:43PM

Hi,i take an error when execute dynamic sql in mysql trigger(actually in sql procedure,procedure is called by triiger.)
mysql version is:5.7.22

Is there any solutions can resolve the problem? Thanks very much.

the code is bellow:

CREATE DEFINER=`sa`@`%` PROCEDURE `sp_insertodstasktable`(in dbName VARCHAR(64),in sourceTable VARCHAR(64),
in keyName VARCHAR(64), in id VARCHAR(64),in statusValue tinyint)
BEGIN
SET @tableName = f_getodstasktablename();
-- CALL sp_createodstasktable(@tableName);

SET @p1 = dbName;
SET @p2 = sourceTable;
SET @p3 = keyName;
SET @p4 = id;
SET @p5 = statusValue;
-- SET @sqlText= CONCAT('INSERT INTO ',@tableName,' values(?,?,?,?,?,NOW())');
PREPARE exec_sql FROM "INSERT INTO ods_task_20200730 values('db1','cms_channel','id','123456','0',NOW())";
EXECUTE exec_sql using @p1,@p2,@p3,@p4,@p5;
DEALLOCATE PREPARE exec_sql;

END

Options: ReplyQuote


Subject
Views
Written By
Posted
Dynamic SQL is not allowed in stored function or trigger
394
July 29, 2020 09:43PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.