List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:February 7 2008 4:07pm
Subject:Re: stored procedure, parameter type help needed
View as plain text  
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);
> ----------------------------------------------
>
Thread
stored procedure, parameter type help neededMagne Westlie7 Feb
  • Re: stored procedure, parameter type help neededPeter Brawley7 Feb
  • Re: stored procedure, parameter type help neededPeter Brawley7 Feb
    • Re: stored procedure, parameter type help neededMagne Westlie7 Feb
      • Re: stored procedure, parameter type help neededPeter Brawley7 Feb
        • Re: stored procedure, parameter type help neededMagne Westlie8 Feb
          • RE: stored procedure, parameter type help neededJerry Schwartz8 Feb
            • Re: stored procedure, parameter type help neededMagne Westlie11 Feb
              • Re: stored procedure, parameter type help neededMoon's Father12 Feb