CREATE Procedure dbo.pc_SelectProc @tblname VarChar(100), @songid varchar(50) AS Declare @SQL VarChar(1000) set @SQL = 'select cap from '+ @tblname + ' where ID='+ @songid Exec (@SQL)How can I convert it to MYSQL 5.0? When I write SET @s = CONCAT('SELECT cap FROM ',tblname1); in the procedure, it works fine. But coming in, the where clause gives an error in code.
MySQL's support for dynamic SQL is currently offered via prepared statements. However, there are some limitations in which parts of a query may be replaced with a place holder. At the moment, table names cannot be set at statement execution time. The solution, then, is to build the query using string functions (such as CONCAT) and set the resulting string as the prepare statement.
Don't forget to change the delimiter when writing stored procedures in the MySQL client, so that it will not attempt to evaluate the expression only through the first semicolon.
DELIMITER // CREATE PROCEDURE pc_SelectProc (IN tblname varchar(100), IN songid varchar(50)) BEGIN SET @SQL := CONCAT('SELECT cap FROM ', tblname, ' WHERE ID = ''', songid, ''''); PREPARE pc_SelectProcStatement FROM @SQL; EXECUTE pc_SelectProcStatement; DEALLOCATE PREPARE pc_SelectProcStatement; END// DELIMITER ;
This was first published in October 2006