Mysql Stored Proc Passing String List

Question

I have this Stored Procedure below, it works if my PostId is Integer field and i send postIdList as a comma separated string '1,2,3,4'... But if I change my PostId to GUID(char(36) it does not work for me. it gives me an syntax error saying

"Error Code: 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 '"2d9d3ebc-6c9f-467a-8181-8a38891756b)' at line 1"

CREATE DEFINER=`root`@`%` PROCEDURE `GetComments`(
         IN  postIdList TEXT    
    )
BEGIN
set @sql = CONCAT('select * from PostComment where PostId in (', postIdList, ') ');
PREPARE q FROM @sql;
execute q;
END$$

this is how i am calling it CAll GetComments( 'fff78ee0-396b-4300-952b-eee72d65261a,fff517dc-a7b7-441e-85bb-b5134828c0c9');

Any Ideas how to resolve this?

Gordon Linoff · Accepted Answer · 2014-06-08 19:48:01Z

1

Your ids are not integers. You need to pass them in with single quotes or do something like this:

set @sql = CONCAT('select * from PostComment where PostId in (''',
                  replace(postIdList, ',', ''','''),
                  ''') ');

That is, replace each comma with a single quote, comma, single quote. And then put quotes at the beginning and end of the list.

answered Jun 8, 2014 at 19:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql Stored Proc Passing String List

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related