MySQL INSERT query not working in Stored Procedure

Question

I've been stuck into a problem for several hours now. I'm building a stored procedure in MYSQL with lots of Inserts in new physical tables. The following is just a sample that I'm unable to get running in my MySQL server.

DROP PROCEDURE IF EXISTS `someProc`$$

CREATE DEFINER=`test`@`%` PROCEDURE `someProc`()
BEGIN


DROP TABLE IF EXISTS table1;

SET @SQL1 = '   CREATE TABLE `table1` (`last_updated` INT); 

                INSERT INTO `table1` (`last_updated`)
                VALUES (1); ';
-- select @SQL1;


PREPARE stmt2 FROM @SQL1;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;

END$$

DELIMITER ;

Upon invoking the stored proc, I get a MySQL error code 1064 saying that a error exists around 'INSERT INTOtable1(last_updated).

But I can run the String contents assigned to the @SQL1 variable as a valid MySQL query and get the table created as well as the value inserted into it too. I've googled for the error code and as suggested in the blogs/documentation pages, I don't think I'm using a reserved keyword here nor I'm inserting any incompatible datatypes. It's plain simple SQL that works outside of the stored procedure setting but fails miserably inside a proc. Can anyone shed some light?

First thought is to remove the space before the (. MySQL can be finicky about spaces before parentheses. — Gordon Linoff
– Gordon Linoff, Commented May 19, 2014 at 18:22

Gordon Linoff · Accepted Answer · 2014-05-19 18:26:50Z

1

prepare prepares one statement. This is clear in the documentation:

The text must represent a single statement, not multiple statements.

You need to split these two operations into two different statements.

answered May 19, 2014 at 18:26

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rstarter Over a year ago

Thanks Gordon for your help. I wish MySQL threw errors exactly specifying the problem rather than the generic codes. It would have been easier for the noobs like me.

Gordon Linoff Over a year ago

@rstarter . . . I agree 1000%. You noticed my original reaction: "Oh it must be that weird thing with MySQL about spaces after functions (even though insert is not a function)." Totally wrong, but then I remembered about prepared statements.

Ganesh bardawal · Accepted Answer · 2023-10-21 19:43:08Z

0

use BI; DROP PROCEDURE VCInplantFactProcedure; DELIMITER // Create Procedure VCInplantFactProcedure() BEGIN
INSERT INTO fact_vcinplant_table (trip_id, dw_loader_id, dw_packer_id) select ts.trip_id,dl.dw_loader_id,dp.dw_packer_id from tbl_ap_trip_stagging ts left join dimension_loader dl on ts.loader_id=dl.loader left join dimension_packer dp on ts.packer_id=dp.packer;

END// DELIMITER ;

select insert working normally through stored procedure not working in my sql kindly help

answered Oct 21, 2023 at 19:43

Ganesh bardawal

1

Collectives™ on Stack Overflow

MySQL INSERT query not working in Stored Procedure

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related