CODEWITHSUNDEEP
mysqlstored-procedures
Kunwar Kishor
Kunwar Kishor

Reputation: 71

Create dynamic stored procedure in mysql with table name only

I am trying to create a dynamic stored procedure for table name only, I mean when I call the stored procedure with table name as parameter it should be display all details of given table name.

I am using this code:-

CREATE PROCEDURE `test1`(IN tab_name VARCHAR(40))
BEGIN
SET @t1 =CONCAT("SELECT * FROM '",tab_name,"' ");
PREPARE stmt3 FROM @t1;
EXECUTE stmt3;
DEALLOCATE PREPARE stmt3;
END

It display error, and the error is:-

MySQL said: Documentation

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 '' at line 3

Any one can help me.

Upvotes: 0

Views: 1032

Answers (1)

Drew
Drew

Reputation: 24959

Back-ticks surround table name.

DROP PROCEDURE IF EXISTS `test1`;
DELIMITER $$
CREATE PROCEDURE `test1`(IN tab_name VARCHAR(40))
BEGIN
    SET @t1 =CONCAT("SELECT * FROM `",tab_name,"`"); -- back-ticks around tbl name
    PREPARE stmt3 FROM @t1;
    EXECUTE stmt3;
    DEALLOCATE PREPARE stmt3;
END$$
DELIMITER ;

or (PHPMyAdmin or those not requiring delimiters):

DROP PROCEDURE IF EXISTS `test1`;
CREATE PROCEDURE `test1`(IN tab_name VARCHAR(40))
BEGIN
    SET @t1 =CONCAT("SELECT * FROM `",tab_name,"`"); -- back-ticks around tbl name
    PREPARE stmt3 FROM @t1;
    EXECUTE stmt3;
    DEALLOCATE PREPARE stmt3;
END

Hopefully you are going to do something different with this stored proc because it seems trivial and will be slower than just calling the select.

Upvotes: 2

Related Questions