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 ;
Dig Deeper on Linux servers
Related Q&A from Scott Noyes
Get suggestions for choosing MySQL character sets and field collations from expert Scott Noyes. Continue Reading
A MySQL expert describes two ways that the multi-master support can be used. Continue Reading
An expert describes where to find information on detection deadlock algorithms in MySQL. Continue Reading