List:General Discussion« Previous MessageNext Message »
From:Magne Westlie Date:February 7 2008 1:58pm
Subject:stored procedure, parameter type help needed
View as plain text  
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