Q

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)
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

Dig deeper on Open source databases

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataCenter

SearchServerVirtualization

SearchCloudComputing

SearchEnterpriseDesktop

Close