2

The goal

Passing multiple values through a single VARCHAR() parameter.

The scenario

On my application there is a procedure that get products from some table. What I need is simple: take three (or whatever) results by id if I pass it. For example: CALL getProducts('1, 2, 3');.

To do this, I'm working with IN clause. Take a look at my query:

BEGIN
    Select product.Name
    From app_products As product
    Where product.Id In (1, 2, 3)
END

How can I pass those 1, 2, 3 by parameter?

What I've already tried

Take a look in my query with its parameter:

CREATE PROCEDURE `getProductsPrices`(IN `ids` VARCHAR(255))
BEGIN
    Select product.Name
    From app_products As product
    Where product.Id In (ids)
END

Now, when I call it:

CALL `getProductsPrices`('1, 2, 3')

The return is:

Warning: Truncated incorrect DOUBLE '1, 2, 3'

Someone has any suggestion for me?

Duplicated?

I think that this topic isn't duplicated because I already searched by the same subject but with no success.

RolandoMySQLDBA
  • 43,883
  • 16
  • 91
  • 132
Guilherme Oderdenge
  • 4,935
  • 6
  • 61
  • 96
  • Looks like a duplicate of http://stackoverflow.com/questions/8149545/pass-array-to-mysql-stored-routine – Declan_K Aug 01 '13 at 19:38
  • @Declan_K The question appears to be the same, but I don't think it is because the solution passed doesn't solved my problem. – Guilherme Oderdenge Aug 01 '13 at 19:40
  • Then you should try again, because these solutions definitely answer your question. You might want to check that your query (build manually) actually returns what you expect. – RandomSeed Aug 01 '13 at 19:43

2 Answers2

3

Probably not really index friendly, but you may be able to use the find_in_set function...

where find_in_set(product.id, ids)

Otherwise you need to parse the delimitted string into a temp table and join with the table.

Dave Carlile
  • 7,347
  • 1
  • 20
  • 23
  • Didn't worked for me. I tried `CALL `getProductsPrices`('1, 2, 3')` but the return is just the first piece of the parameter (in our case, 1). – Guilherme Oderdenge Aug 01 '13 at 19:42
  • Might need to typecast product.id to a string. Rolando's answer will probably work better though, and should deal with indexes properly. – Dave Carlile Aug 01 '13 at 19:51
2

You need to set up dynamic SQL

DELIMITER $$
DROP PROCEDURE `getProductsPrices` $$
CREATE PROCEDURE `getProductsPrices`(IN `ids` VARCHAR(255))
BEGIN
    SET @sql = 'Select product.Name From app_products';
    SET @sql = CONCAT(@sql,' As product Where product.Id In (',`ids`,')');
    PREPARE s FROM @sql;
    EXECUTE s;
    DEALLOCATE PREPARE s;
END $$
DELIMITER ;

Give it a Try !!!

RolandoMySQLDBA
  • 43,883
  • 16
  • 91
  • 132