1

I'd like to build a dynamic IN Statement within a MySQL Stored Proc. I've tried a number of ways but am unable to get something that works. This is the closest I've come to success, but it only process the first value in the list:103.

I would prefer to use a stored proc and not to build this SQL statement completely in the client and then execute it. Does anyone know the general rules for dynamically building SQL statements in a stored procedure, esp in regards to the IN Statement?

call foo2('103,104,105,106');

Here is the stored proc:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`%` PROCEDURE `foo2`(
szList VARCHAR(256)
)
BEGIN

SELECT 
    E_Id, EX_Value

FROM 
    E

WHERE 
    EX_Value IN (szList);

END
Charles
  • 50,943
  • 13
  • 104
  • 142
paiego
  • 3,619
  • 34
  • 43

1 Answers1

2

what about use FIND_IN_SET for MySQL. I think this link may help you ( How to pass a list of IDs to MySQL stored procedure? )

The crucial part is to define 

szList as TEXT

and use 

FIND_IN_SET(Ex_Value, szlist)

instead of Ex_Value IN (szlist)

Community
  • 1
  • 1
linpingta
  • 2,324
  • 2
  • 18
  • 36
  • This approach works well only on a small data set, because the server will have to read every row of the target table to evaluate whether each value in the column is in the set... it is not going to be able to use an index to find the appropriate rows, so it will not scale properly. – Michael - sqlbot Mar 06 '14 at 12:34
  • @Michael-sqlbot, I think IN could use index in SQL, does it special in procedure? I am not sure, please tell me if I am wrong. – linpingta Mar 06 '14 at 15:44
  • `IN()` can use an index, but `FIND_IN_SET()` can't. – Michael - sqlbot Mar 06 '14 at 20:48