From: Peter Brawley
Date: February 7 2008 4:07pm
Subject: Re: stored procedure, parameter type help needed
List-Archive: http://lists.mysql.com/mysql/211247
Message-Id: <47AB2CB4.1000500@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Magne,

 >I want to create a stored procedure that runs a query using the "IN" 
operator ...

See 'Variable-length argument for query IN() clause' at 
http://www.artfulsoftware.com/queries.php

PB

-----

Magne Westlie wrote:
> Dear List,
>
> I want to create a stored procedure that runs a query using the "IN" 
> operator (or is "IN" a function???) on values sent as argument. That 
> is, my procedure should be called with something like:
>
> CALL get_users((1,2));
>
> and I was hoping to implement something like this:
>
> CREATE PROCEDURE get_users(uids "LIST") -- what type to use here?
> BEGIN
>   SELECT * FROM user_test WHERE user_id IN uids;
> END::
>
>
> Thanks,
>
> Magne Westlie
>
>
> Working test code for getting one user only:
> ----------------------------------------------
> DROP TABLE IF EXISTS user_test;
> CREATE TABLE user_test (
>   user_id INT,
>   user_name VARCHAR(100),
>   PRIMARY KEY (user_id)
> ) ENGINE=MyIsam;
>
> INSERT INTO user_test VALUES(1, 'Bob');
> INSERT INTO user_test VALUES(2, 'Ann');
> INSERT INTO user_test VALUES(3, 'Bill');
>
> DROP PROCEDURE IF EXISTS get_users;
> DELIMITER ::
> CREATE PROCEDURE get_users(uid INT)
> BEGIN
>   SELECT * FROM user_test WHERE user_id = uid;
> END::
> DELIMITER ;
>
> CALL get_users(3);
> ----------------------------------------------
>