Problem solve Get help with specific problems with your technologies, process and projects.

Converting SQL Server procedure to MySQL 5.0

MySQL expert Scott Noyes provides code on how to convert a procedure from SQL Server 2000 to MySQL 5.0.

I have a procedure in SQL SERVER 2000:
CREATE Procedure dbo.pc_SelectProc
@tblname VarChar(100),
@songid varchar(50)
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.

CREATE PROCEDURE pc_SelectProc (IN tblname varchar(100), IN songid varchar(50))
  SET @SQL := CONCAT('SELECT cap FROM ', tblname, ' WHERE ID = ''',
songid, '''');
  PREPARE pc_SelectProcStatement FROM @SQL;
  EXECUTE pc_SelectProcStatement;
  DEALLOCATE PREPARE pc_SelectProcStatement;

Dig Deeper on Linux servers

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.