stored procedure for display table data( select * from table name)

rohitamitpathak · #1 (**permalink**) 03-07-11, 05:59

good afternoon sir,
i am trying to create a stored procedure which is dynamically show the detail of table..
for showing a table we use select * from tablename;
i want to make it happen with help of stored procdure where u pass the table name as a IN parameter and u will display the table data - i wrote this code for creating procedure

DELIMITER $$
DROP PROCEDURE IF EXISTS `zero`.`gettablevalues`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `gettablevalues`(IN table_name varchar(100))
BEGIN
select * from table_name;
END$$

DELIMITER ;
i know i can not use IN parameter variable like this, ( i should use this with = operator) , what i write i m not getting... can any body help me..
------------> i got the error that table is not exist in database... whrere i m doing wrong.. pls let me know my mistake

it-iss.com · #2 (**permalink**) 03-07-11, 10:01

I understand what you are trying to do but if you look at performance this is going to create an overhead. Every call to the stored procedure is going to place information onto the stack call the stored procedure, dynamically generate the SQL, parse the statement and finally execute it.

If this is called once this will be negligible, however, if this is called many times then you could run into performance issues. You should access the data directly i.e. SELECT * FROM <tablename> rather than doing it through a stored procedure.

In any case, the code for creating this is:

Code:

DROP PROCEDURE IF EXISTS aProc;
DELIMITER $$
CREATE PROCEDURE aProc(tableName VARCHAR(30))
BEGIN
	SET @s = CONCAT('SELECT * FROM ', tableName);
	PREPARE stmt FROM @s;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END
$$
DELIMITER ;

rohitamitpathak · #3 (**permalink**) 03-08-11, 01:37

thank you so much sir, thank you so much,
it work fine... sir can u suggest me any book or any site to learn about mysql stored procedure syntax and semantics...

rohitamitpathak · #4 (**permalink**) 03-08-11, 01:39

Quote:

Originally Posted by it-iss.com

I understand what you are trying to do but if you look at performance this is going to create an overhead. Every call to the stored procedure is going to place information onto the stack call the stored procedure, dynamically generate the SQL, parse the statement and finally execute it.

If this is called once this will be negligible, however, if this is called many times then you could run into performance issues. You should access the data directly i.e. SELECT * FROM <tablename> rather than doing it through a stored procedure.

In any case, the code for creating this is:

Code:

DROP PROCEDURE IF EXISTS aProc;
DELIMITER $$
CREATE PROCEDURE aProc(tableName VARCHAR(30))
BEGIN
	SET @s = CONCAT('SELECT * FROM ', tableName);
	PREPARE stmt FROM @s;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END
$$
DELIMITER ;

it woks fine, i m looking for exactly this ....thank you

Internet Services

Web Development

Digital Marketing

Consumer Tech