Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2011
    Location
    india- new delhi
    Posts
    31

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

    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
    Last edited by rohitamitpathak; 03-07-11 at 08:57.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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 ;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Feb 2011
    Location
    india- new delhi
    Posts
    31

    thank you so much

    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...

  4. #4
    Join Date
    Feb 2011
    Location
    india- new delhi
    Posts
    31

    thnk u so much sir

    Quote Originally Posted by it-iss.com View Post
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •