MySQL Forums
Forum List  »  InnoDB

why wont this work!!
Posted by: Barry O' Neill
Date: June 29, 2009 08:17AM

Hey guys,
I have this procedure written to trim all rows in a given table. But it wil not work it keeps trying to find a table name equal to the variable name rather than a table name equal to the value of the variable. Any help is appreciated. I understand that a table name cannot be dynamic in a cursor definition but in this case the table name is already specified and the parameter is in the where clause. Why wont this work.

DELIMITER $$

DROP PROCEDURE IF EXISTS `barry`.`trimAllCols`$$

CREATE PROCEDURE `trimAllCols`(IN tableName VARCHAR(255))
BEGIN
DECLARE no_more_records INT DEFAULT 0;
DECLARE col_name VARCHAR(255);
DECLARE col_cursor CURSOR FOR
SELECT column_name FROM information_schema.columns WHERE table_name = tableName;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_records = 1;

OPEN col_cursor;
FETCH col_cursor INTO col_name;
REPEAT

UPDATE tableName SET col_name = TRIM(col_name);

FETCH col_cursor INTO col_name;
UNTIL no_more_records = 1
END REPEAT;
CLOSE col_cursor;
END$$

DELIMITER ;
*******************************************

CALL trimAllCols('testtable');

gives the following error
Error Code : 1146
Table 'barry.tablename' doesn't exist

Options: ReplyQuote


Subject
Views
Written By
Posted
why wont this work!!
3474
June 29, 2009 08:17AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.