0

Can anybody help me to find a mysql corespondent to the following MS SQL syntax?

CREATE PROCEDURE testsp @COLUMN_NAME VARCHAR(20), @COLUMN_VALUE VARCHAR(20)
AS

DECLARE @SQL VARCHAR(40)  
SET @SQL = 'UPDATE TableX set ''+@COLUMN_NAME+ ''=''+@COLUMN_VALUE+'' '
PRINT (@SQL)
EXEC (@SQL)

Is it possible to translate this code into Mysql?

Thanks!

user1391078
  • 53
  • 1
  • 2
  • 9
  • Is this code missing a procedure name right after CREATE PROCEDURE? – Jeremy Goodell Jun 05 '12 at 19:49
  • Does [this](http://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure) provide any insight? – vergenzt Jun 05 '12 at 19:55
  • Have you looked at [the manual](http://dev.mysql.com/doc/en/create-procedure.html)? [Stack Overflow is not a code translation service](http://meta.stackexchange.com/a/129362/161492). – eggyal Jun 05 '12 at 19:56
  • @user1391078 As a new stack overflow user, you may not be aware that when you are satisfied with an answer, you should check the checkmark next to it. This is known as "accepting" an answer and provides an increase in reputation for the answerer. You may accept only one answer per question. – Jeremy Goodell Jun 05 '12 at 21:04

1 Answers1

2
CREATE PROCEDURE testsp (COLUMN_NAME VARCHAR(20), COLUMN_VALUE VARCHAR(20))
BEGIN
    SET @SQL_COMMAND = CONCAT('UPDATE TableX SET ', COLUMN_NAME, ' = ?');
    SELECT @SQL_COMMAND; /* prints the command */
    PREPARE SQL_STMT FROM @SQL_COMMAND;
    EXECUTE SQL_STMT USING COLUMN_VALUE;
    DEALLOCATE SQL_STMT;
END
Jeremy Goodell
  • 18,225
  • 5
  • 35
  • 52
  • 1
    Will `PREPARE` automatically inject string delimiters around `COLUMN_VALUE`? – Aaron Bertrand Jun 05 '12 at 20:11
  • You're right, those are missing. I'll add them now. But I don't see the point of putting a string delimiter around the column name. – Jeremy Goodell Jun 05 '12 at 20:13
  • OK, I changed it to actually use a bound parameter value for the COLUMN_VALUE. Thus, the COLUMN_VALUE string will be handled correctly even if it contains special characters. – Jeremy Goodell Jun 05 '12 at 20:18
  • AND, it prevents against SQL injection, at least for COLUMN_VALUE. If these values are coming from user input, then COLUMN_NAME would need to be parsed and validated separately since bound values can only be used for data values, not for SQL identifiers and keywords. – Jeremy Goodell Jun 05 '12 at 20:21