mysql prepared statement unable to execute. Getting error 1064

Question

DELIMITER $$

DROP PROCEDURE IF EXISTS TOUR_TRANSFER $$

CREATE PROCEDURE TOUR_TRANSFER(in_search CHAR(80),tbl CHAR(80),clmn CHAR(80))
BEGIN
DECLARE selstmt,sel_cmd VARCHAR(100);
BEGIN -- for rubric table
        SET selstmt=concat('SELECT ',clmn,' INTO @tbl_data FROM ',tbl,' WHERE ',clmn, ' REGEXP "', in_search,'"');
        select selstmt;
        PREPARE sel_cmd FROM @selstmt;
        EXECUTE sel_cmd;
        select @tbl_data;
        DEALLOCATE PREPARE sel_cmd;
END;
END$$
DELIMITER ;

I am getting an error (1064) in the prepare statement whereas I am able to execute it manually.

Also, please share your code - perhaps you having a mistake in the code side and not on the SQL side. — Mureinik, Jan 04 '15 at 07:19

score 0 · Answer 1 · edited May 23 '17 at 12:05

Swap your first two lines and change them to look like this. You want to drop the procedure first, then set the delimiter for your stored proc.

DROP PROCEDURE IF EXISTS TOUR_TRANSFER;

DELIMITER $$

You also appear to have an extra set of BEGIN and END statements. You can remove the inner ones.

That would get your code looking like this.

DROP PROCEDURE IF EXISTS TOUR_TRANSFER;

DELIMITER $$

CREATE PROCEDURE TOUR_TRANSFER(in_search CHAR(80),tbl CHAR(80),clmn CHAR(80))
BEGIN
DECLARE selstmt,sel_cmd VARCHAR(100);
        SET selstmt=concat('SELECT ',clmn,' INTO @tbl_data FROM ',tbl,' WHERE ',clmn, ' REGEXP "', in_search,'"');
        select selstmt;
        PREPARE sel_cmd FROM @selstmt;
        EXECUTE sel_cmd;
        select @tbl_data;
        DEALLOCATE PREPARE sel_cmd;
END

$$

DELIMITER ;

Here's a link to another question related to using prepared statements within a stored proc. See the answer for an example.

MySQL PREPARE statement in stored procedures

If I remove the $$ on the line `DROP PROCEDURE IF EXISTS TOUR_TRANSFER $$` I get a syntax error The new code is `DELIMITER $$ DROP PROCEDURE IF EXISTS TOUR_TRANSFER $$ CREATE PROCEDURE TOUR_TRANSFER(in_search CHAR(80),tbl CHAR(80),clmn CHAR(80)) BEGIN DECLARE selstmt,sel_cmd VARCHAR(100); SET selstmt=concat('SELECT ',clmn,' INTO @tbl_data FROM ',tbl,' WHERE ',clmn, ' REGEXP "', in_search,'"'); select selstmt; PREPARE sel_cmd FROM @selstmt; EXECUTE sel_cmd; select @tbl_data; DEALLOCATE PREPARE sel_cmd; END$$ DELIMITER ; call TOUR_TRANSFER('avinash','user','name') ` — Avinash Singh, Jan 04 '15 at 07:45

score 0 · Answer 2 · answered Jan 04 '15 at 07:29

This is because there's a syntax error in your code. Befin Statement should have an end and from your code the end statement is commented out see sample code below

DELIMITER $$;

DROP PROCEDURE IF EXISTS TOUR_TRANSFER $$;

CREATE PROCEDURE TOUR_TRANSFER(in_search CHAR(80),tbl CHAR(80),clmn CHAR(80)) BEGIN DECLARE selstmt,sel_cmd VARCHAR(100); 
BEGIN 
-- for rubric table SET selstmt=concat('SELECT ',clmn,' INTO @tbl_data FROM ',tbl,' WHERE ',clmn, ' REGEXP "', in_search,'"'); select selstmt; PREPARE sel_cmd FROM @selstmt; EXECUTE sel_cmd; select @tbl_data; DEALLOCATE PREPARE sel_cmd; 
END; END$$ DELIMITER

Add a new line on you code and it should work fine.

When I call the stored procedure after creating it then I get a prepared statement produced using 'SET selstmt=concat('SELECT ',clmn,' INTO @tbl_data FROM ',tbl,' WHERE ',clmn, ' REGEXP "', in_search,'"'); select selstmt;'line in the code. If I execute the generated statement manually(separately) it works well but it does not run in the stored procedure — Avinash Singh, Jan 04 '15 at 08:15

Vicky · Accepted Answer · 2015-01-04T08:37:52.430

0

A few syntactical issues. Try this one now

DELIMITER //
DROP PROCEDURE IF EXISTS TOUR_TRANSFER;
//
CREATE PROCEDURE TOUR_TRANSFER(in_search CHAR(80),tbl CHAR(80),clmn CHAR(80))
BEGIN
DECLARE selstmt, tbl_data, sel_cmd VARCHAR(100);
SET @selstmt=concat('SELECT ',clmn,' INTO @tbl_data FROM ',tbl,' WHERE ',clmn, '   REGEXP "', in_search,'"');
select @selstmt;
PREPARE sel_cmd FROM @selstmt;
EXECUTE sel_cmd;
select @tbl_data;
DEALLOCATE PREPARE sel_cmd;
END;
//
DELIMITER ;

edited Jan 04 '15 at 08:37

answered Jan 04 '15 at 07:50

Vicky

871
9
16

I am getting the same error \n 13:33:10 call TOUR_TRANSFER('avinash','user','name') Error Code: 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 'NULL' at line 1 – Avinash Singh Jan 04 '15 at 08:04
there is some issue in the prepare statement I am able to create the procedure without any syntax errors but unable to call it as it throws 1064 error – Avinash Singh Jan 04 '15 at 08:17

mysql prepared statement unable to execute. Getting error 1064

3 Answers3