Unknown error when runing stored procedure

[Adrian Sosialuk]

Hi,

I have written a small procedure to easy my life. It searches for tables
and/or column names in a database using information_schema system table.

Here it is:

DELIMITER //

CREATE PROCEDURE `find` (IN what VARCHAR(100), IN how TINYINT UNSIGNED)
BEGIN

CASE how
WHEN 0 THEN
SELECT information_schema.columns.table_name AS "Table", information_schema.columns.column_name AS "Column"
FROM information_schema.columns, information_schema.tables
WHERE information_schema.columns.table_schema = 'my_database'
AND (information_schema.columns.table_name LIKE CONCAT('%', what, '%')
OR information_schema.columns.column_name LIKE CONCAT('%', what, '%'))
AND information_schema.columns.table_name = information_schema.tables.table_name
AND information_schema.tables.table_type = 'BASE TABLE'
ORDER BY information_schema.columns.table_name, information_schema.columns.column_name;
WHEN 1 THEN
SELECT DISTINCT information_schema.tables.table_name AS "Table"
FROM information_schema.tables
WHERE information_schema.tables.table_schema = 'my_database'
AND information_schema.tables.table_name LIKE CONCAT('%', what, '%')
AND information_schema.tables.table_type = 'BASE TABLE'
ORDER BY information_schema.tables.table_name;
WHEN 2 THEN
SELECT information_schema.columns.table_name AS "Table", information_schema.columns.column_name AS "Column"
FROM information_schema.columns, information_schema.tables
WHERE information_schema.columns.table_schema = 'my_database'
AND information_schema.columns.column_name LIKE CONCAT('%', what, '%')
AND information_schema.columns.table_name = information_schema.tables.table_name
AND information_schema.tables.table_type = 'BASE TABLE'
ORDER BY information_schema.columns.column_name;
ELSE
SELECT 'Imroper use of last parameter: please provide 0, 1 or 2' AS Error;
END CASE;

END //

DELIMITER ;

The database consists of several hundred tables and as a new starter writing
reports without specs can be a nightmare when you have to find missing
tables and/or column names. This procedure helps a lot but unfortunatelly
I get Unknown Error 1105 very often. Strange think is that the same query
might run properly and another time not. I'm working on a local database
so nobody else is using it while running the procedure.
Version is 5.0.27-community on Windows XP Pro SP2 platform.

Thanks for any info.

Cheers,

Adrian

PS. Is there a way to add an index to system tables ?