Paramaters and MySQL Store Procedures

[random1]

I have created the stored procedure:

 

CREATE PROCEDURE `procedure_search`(IN `pTableName` VARCHAR(50), IN `pFieldName` VARCHAR(50), IN `pSearchTerm` VARCHAR(50), IN `pOrderTerm` VARCHAR(50))
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'A procedure to search tables.'
BEGIN
/* Searches a table or tables for a search term */
SELECT * FROM pTableName WHERE pFieldName
LIKE pSearchTerm ORDER BY pOrderTerm;
END

 

I'm trying to create a generic, useful MySQL search function.

 

How can I fix it to stop complaining about 'pTableName' when run and also fix it so that the like statement has '%' correctly for syntax for the LIKE statement?

 

CALL `procedure_search`('user', 'user_username',
'dean', 'user_username');

 

I get the error message:

 

/* SQL Error (1146): Table 'database_core.ptablename' doesn't exist */

[fenway]

Why are you using backticks?

[Mchl]

You can't get table name from procedure parameter in current MySQL versions. There's an ugly way around this however. You need to create a prepared statement inside your procedure:

 

DELIMITER $$

DROP PROCEDURE IF EXISTS `p_take` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `p_take`(IN tableName VARCHAR(100))
BEGIN


  SET @stmt = CONCAT('SELECT * FROM ',tableName,' WHERE 1');


  PREPARE stmt FROM @stmt;
  EXECUTE stmt;

END $$

DELIMITER ;

 

 

[random1]

I'm using backticks because I've read they are best practice:

 

-- Using "backticks" on database and column names avoids any issues where the entity name is a reserved word --

 

For example I can have a table called 'user' but I surround it in `` it avoids any conflict with the reserved word 'user'.

 

@Mchl : That's a pretty big work around :S ... I'll have a look at it and see if I can apply it to my code.

 

Thanks for the help

 

 

[Mchl]

-- Using "backticks" on database and column names avoids any issues where the entity name is a reserved word --

 

Best practice is to avoid using reserved words as entity names.

[random1]

Thanks Mchl that worked. Not a bad workaround at all I found out.

 

All the Best,

 

- Dean

[Mchl]

Yeah. Actually I didn;t think of that before, and I find much better than any proposed before.