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/[EMAIL PROTECTED]
