List:General Discussion« Previous MessageNext Message »
From:Magne Westlie Date:February 8 2008 10:37am
Subject:Re: stored procedure, parameter type help needed
View as plain text  
Hi again,

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 (
   user_id INT,
   user_name VARCHAR(100),
   PRIMARY KEY (user_id)
) ENGINE=MyIsam;
INSERT INTO user_test VALUES(1,'Bob'), (2,'Ann'), (3,'Bill'), (4,'Jim');

DROP PROCEDURE IF EXISTS get_users;
DELIMITER ::
CREATE PROCEDURE get_users(IN param VARCHAR(1000))
BEGIN
   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;
   EXECUTE stmt;

   SELECT user_id, user_name FROM user_test WHERE user_id IN (SELECT uid 
FROM temp_uids);
   DROP PREPARE stmt;
END; ::
DELIMITER ;

CALL get_users('(2), (3)');
-----------------------------------------------------------


MW


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.
> 
> PB
> 
> -----
> 
> Magne Westlie wrote:
>> Hi,
>>
>> 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?
>>
>> Magne
>>
>>
>>
>> Peter Brawley wrote:
>>> Magne,
>>>
>>> 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 
>>> statement:
>>> |
>>> DROP PROCEDURE IF EXISTS passInParam;
>>> DELIMITER |
>>> CREATE PROCEDURE passInParam( IN qry VARCHAR(100), IN param 
>>> VARCHAR(1000) )
>>> BEGIN
>>>  SET @qry = CONCAT( qry, param, ')' );
>>>  PREPARE stmt FROM @qry;
>>>  EXECUTE stmt;
>>>  DROP PREPARE stmt;
>>> END;
>>> |
>>> 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''' ); |
>>> ||
>>> ||||PB
>>>
>>>
>>>>
>>>
>>
>>
> 
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