[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: Stored_Procedure/table_names and from table_name errors
From:       Ow Mun Heng <Ow.Mun.Heng () wdc ! com>
Date:       2006-10-29 14:44:33
Message-ID: 1162133073.8326.12.camel () neuromancer ! home ! net
[Download RAW message or body]

On Sun, 2006-10-29 at 22:06 +0800, Ow Mun Heng wrote:
> Hi,
> 
> Learning about MySQL's stored procedure programming.
> 
> trying to do things like count(*) of the # of rows in a specific table.
> When I try to put the table_names as a variable I don't get anything
> 
> eg:
> DELIMITER $$
> DROP PROCEDURE IF EXISTS `dbxmldmysql`.`sp_countrows2`$$
> CREATE PROCEDURE `dbxmldmysql`.`sp_countrows2` (in_table_name
> varchar(30))
> BEGIN
>   SELECT count(*) AS 'QTY' FROM in_table_name;
> END$$
> DELIMITER ;
> 
> why does it expect a literal name in the "FROM" clause?

I found the answer via the mysql forums 
http://forums.mysql.com/read.php?98,105468,105468#msg-105468
http://dev.mysql.com/tech-resources/articles/mysql-storedproc.html

The new sp is nowDELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`sp_countrows2`$$
CREATE PRODECURE `sp_countrows2`(in_table_name varchar(30))
    READS SQL DATA
BEGIN
  SET @s =  CONCAT('SELECT count(*) AS "', in_table_name, '(QTY)" FROM
', in_table_name);
  PREPARE stmt FROM @s;
  EXECUTE stmt;
END$$
DELIMITER ;

Is there another way to skin this cat?

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=mysql@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]