0

I have written following Mysql stored procedure with 2 input parameters (ids and tags)

SET @sql = Concat('UPDATE tbl_Members SET TagId=CONCAT(TagId,''',tags,''') WHERE MemberID IN (',ids,')');PREPARE stmt FROM @sql;EXECUTE Stmt;

enter image description here

When I try to execute that it throws the following error

The following query has failed: "CREATE DEFINER=`0zrt`@`localhost` PROCEDURE `update_member_tag`(IN `ids` VARCHAR(255), IN `tags` VARCHAR(255)) NOT DETERMINISTIC MODIFIES SQL DATA SQL SECURITY DEFINER SET @sql = CONCAT('UPDATE tbl_Members SET TagId=CONCAT(TagId,''',tags,''') WHERE MemberID IN (',ids,')'); PREPARE stmt FROM @sql; EXECUTE stmt;"
MySQL said: #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 'PREPARE stmt FROM @sql; EXECUTE stmt' at line 2
user580950
  • 3,558
  • 12
  • 49
  • 94
  • Avoid [list of doom](http://gtowey.blogspot.com/2009/12/how-to-fix-comma-separated-list-of-doom.html) structures. Your queries will be horrible and slow like the procedure you are attempting to define. – danblack Nov 20 '18 at 21:05
  • @danblack Is there a better way to avoid that in a stored procedure? – user580950 Nov 20 '18 at 21:08
  • It has nothing to do with the procedure, it's a database design problem. – Barmar Nov 20 '18 at 21:27
  • https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – Barmar Nov 20 '18 at 21:27
  • Please post the whole procedure in the question. But it looks like the problem is that you're missing `BEGIN` and `END` around the procedure body. – Barmar Nov 20 '18 at 21:28
  • And you generally avoid stored procedures by not using them. Application code can do SQL quite well without it. You're not obliged to use stored procedures just because they exist. They are horrible to write and debug. – danblack Nov 20 '18 at 21:29

0 Answers0