Creating a MySQL stored procedure to update records

Question

I'm converting all of my existing MSSQL databases and stored procedures am stuck on a new stored procedure where I need to update an existing record. The procedure gets called from a web form once a record has been inserted into the database and en email sent successfully (or at least passed off to the SMTP server)

I've had a working procedure in MSSQL for a long time but am trying to convert it to MySQL. I'm passing in 3 variables - a bit indicating the email got sent, a string indicating which SMTP server has been used to sent the email and a unique record id so I'll know what record to update. I'm also adding the date and time to another field to know when the procedure ran.

I've got the following but keep getting an error "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 7 - yet I don't see anything off at line 7 - at least to my eye.

The code I'm trying to use is:

CREATE PROCEDURE `sp_Test`(
  `emailSent_In` BIGINT UNSIGNED,
  `emailTransport_In` VARCHAR(100),
  `formSecret_In` VARCHAR(32)
)
BEGIN
  SET @`query` := CONCAT('UPDATE ',`tbl_JustSayThanks`,' 
  SET `emailSent` = `emailSent_In`,
  `emailTransport` = ',`emailTransport_In`,',
`emailSentDate` = NOW()
    WHERE `formSecret` = ', `formSecret_In`, '');
  PREPARE `stmt` FROM @`query`;
  EXECUTE `stmt`;
       @`query` := NULL;
  DEALLOCATE PREPARE `stmt`;
END//
DELIMITER ;

Just FYI, I'm using the CONCAT based on a previous answer I received from wchiquito and will be passing in the table name eventually. But, I wanted to get it to work on a simplified level before going there.

Creating a MySQL stored procedure to update records

1 Answers