Ask the Expert

Converting SQL Server procedure 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.

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: