CODEWITHSUNDEEP
phpmysqlstored-procedures
user2850305
user2850305

Reputation: 397

stored procedure not working php mysql

I am trying to write a simple stored procedure for insert with parameter as table name and insert value.

DELIMITER $$
DROP PROCEDURE IF EXISTS TEST3; 
CREATE  PROCEDURE TEST3(IN tab_name VARCHAR(40),IN IDVALUE VARCHAR(40))
BEGIN
 SET @t2 = CONCAT('INSERT INTO ? (id) VALUES(?)');
 PREPARE stmt4 FROM @t2;
 EXECUTE stmt4 USING @tab_name, @IDVALUE;
 DEALLOCATE PREPARE stmt4;
END  $$
DELIMITER ;

It is being created but when I call it it throws following error:

ERROR 1064 (42000): 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 '? (id) VALUES(?)' at line 1

What is that I am doing wrong.

Upvotes: 1

Views: 904

Answers (2)

Sakthikanth
Sakthikanth

Reputation: 139

Try running this query, it worked for me in phpmyadmin

CREATE PROCEDURE `sample_procedure`(IN `10` INT) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER SELECT "print this";

Upvotes: 0

peterm
peterm

Reputation: 92785

If you need this you can do it like this

DELIMITER $$
CREATE  PROCEDURE TEST3(IN tab_name VARCHAR(40), IN IDVALUE VARCHAR(40))
BEGIN
 SET @t2 = CONCAT('INSERT INTO `', tab_name, '` (id) VALUES(?)'),
     @id = idvalue;

 PREPARE stmt4 FROM @t2;
 EXECUTE stmt4 USING @id;
 DEALLOCATE PREPARE stmt4;
END$$
DELIMITER ;

Here is a SQLFiddle demo

Two major issues with your original code are:

  1. You can't prepare a statement without an object identifier, table name in your case. Object identifiers are static. To work around this you should concatenate a table name into your statement.

  2. Local (IDVALUE in your case) and user (session) variables (@IDVALUE) are two completely different beasts. And EXECUTE ... USING can work only with latter. Therefore you need to assign a value of IDVALUE to a session variable (@id in my example).

Further reading:

Upvotes: 3

Related Questions