0

Query is retrieving correct result but in Prepared statement getting error "Error Code: 1064. check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO audittrail(BATCHID,IPADDRESS,DATE,LINENAME,ACTION) values (1165' at line 2" Thanks in advance:-)

CREATE DEFINER = 'root'@'localhost'
PROCEDURE new_procedure()
BEGIN

DECLARE stmt text;

SET @stmt = (SELECT CONCAT('START TRANSACTION; \n', GROUP_CONCAT(objdata SEPARATOR ';\n'),';\nCOMMIT; \n DELETE FROM qry_obj_dtl where id in (',GROUP_CONCAT(ID SEPARATOR','),');') AS objData1 FROM qry_obj_dtl);

SELECT @stmt;

PREPARE param_stmt FROM @stmt;

EXECUTE param_stmt;

DEALLOCATE PREPARE param_stmt;
  
END
Improve this question
6
  • 'Executed below procedure ' - did you?- then you must have transcribed it incorrectly because what you have published does not syntax..Also publish sample data and expected outcome as text to clarify. Commented Jan 20, 2023 at 14:21
  • use temporary table and then drop it. Commented Jan 22, 2023 at 23:33
  • Getting error 1064 in prepared statement. My result from query contains result as below. START TRANSACTION; INSERT INTO audittrail(BATCHID,IPADDRESS,DATE,LINENAME,ACTION) values (11654565,"MAT-IT-1607 (00)","2000-00-00","xxxxxx","xxxxxx(MAT-IT-1607 (00)). PRN file(pallet lable final eu.prn) changed. Level : Pallet by xxxxxx Remark: ok"); COMMIT; DELETE FROM qryobjdtl where id in (11654565); Commented Jan 23, 2023 at 10:05
  • @P.Salmon Data added. Commented Jan 24, 2023 at 10:33
  • You can only submit 1 statement at a time to dynamic sql.. Commented Jan 24, 2023 at 10:35
Related questions
134
How to only update specific git submodules?
9
Is it possible to force git submodules to be updated?
12
How do I update a git submodule without checking it out
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.