List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:February 8 2008 4:10pm
Subject:RE: stored procedure, parameter type help needed
View as plain text  
> -----Original Message-----
> From: Magne Westlie [mailto:magne@stripped]
> Sent: Friday, February 08, 2008 5:37 AM
> To: peter.brawley@stripped
> Cc: mysql@stripped
> Subject: Re: stored procedure, parameter type help needed
>
> 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);
[JS] Couldn't you replace the " WHERE user_id IN (SELECT uid FROM
temp_uids)"
with a simple JOIN? If "IN" is badly optimized, as I've read here, wouldn't
that be more efficient? Or am I (as usual) missing something?

>    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
> >>>
> >>>
> >>>>
> >>>
> >>
> >>
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> infoshop.com




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