I found a way that works for the query I wanted in my sproc. It uses
your solution to insert into a temporary table, and then uses a SELECT
in the "IN"-part.
I don't know yet if this solution may have side-effects. I need to read
more about how temporary tables is handled by MySQL, as when it comes to
speed of execution in this solution.
Thanks for your help Peter.
My solution (with an argument that may look weird, but I found out
adding the extra paranthesis while generating the string in Python was
so much easier that doing it in the sproc) (working test):
DROP TABLE IF EXISTS user_test;
CREATE TABLE user_test (
PRIMARY KEY (user_id)
INSERT INTO user_test VALUES(1,'Bob'), (2,'Ann'), (3,'Bill'), (4,'Jim');
DROP PROCEDURE IF EXISTS get_users;
CREATE PROCEDURE get_users(IN param VARCHAR(1000))
DROP TEMPORARY TABLE IF EXISTS temp_uids;
CREATE TEMPORARY TABLE temp_uids (
uid INT NOT NULL
SET @qry = CONCAT('INSERT INTO temp_uids VALUES ', param);
PREPARE stmt FROM @qry;
SELECT user_id, user_name FROM user_test WHERE user_id IN (SELECT uid
DROP PREPARE stmt;
CALL get_users('(2), (3)');
Peter Brawley wrote:
> Hi Magne
> >...the query I actually want to use this in, is a 100 line query with
> lots of arguments.
> >I don't feel to good about creating it into a bunch of strings (16)
> that I have to
> >concatenate with the variables inbetween.
> The only alternative I know for current versions of MySQL is to assemble
> the query in the app layer.
> >Also, I was moving the query into a stored procedure because I wanted
> to make
> >the request fast, and the concatenating and string handling takes some
> of that away.
> Unfortunately MySQL sprocs do not yet deliver this advantage.
> Magne Westlie wrote:
>> Thanks a lot Peter, that was useful and it worked fine. The only
>> problem is that the query I actually want to use this in, is a 100
>> line query with lots of arguments. I don't feel to good about creating
>> it into a bunch of strings (16) that I have to concatenate with the
>> variables inbetween.
>> Also, I was moving the query into a stored procedure because I wanted
>> to make the request fast, and the concatenating and string handling
>> takes some of that away.
>> Is there another way?
>> Peter Brawley wrote:
>>> Sorry, the server is down at the moment, here is the entry ...
>>> To have an sproc accept a variable-length parameter list for an
>>> |IN(...)| clause in a query, code the sproc to |PREPARE| the query
>>> DROP PROCEDURE IF EXISTS passInParam;
>>> DELIMITER |
>>> CREATE PROCEDURE passInParam( IN qry VARCHAR(100), IN param
>>> VARCHAR(1000) )
>>> SET @qry = CONCAT( qry, param, ')' );
>>> PREPARE stmt FROM @qry;
>>> EXECUTE stmt;
>>> DROP PREPARE stmt;
>>> DELIMITER ;
>>> For this example, the query string should be of the form:
>>> SELECT ... FROM ... WHERE ... IN ( |
>>> but so long as it has those elements, it can be as complex as you
>>> like. When you call the sproc:
>>> 1. Quote each argument with a /pair/ of single quotes,
>>> 2. Separate these quoted arguments with commas,
>>> 3. Surround the whole |param| string with another set of single quotes:
>>> CALL passInParam( 'SELECT * FROM tbl WHERE colval IN (',
>>> ('''abc'',''def'',''ghi''' ); |