> -----Original Message-----
> From: Magne Westlie [mailto:[EMAIL PROTECTED]
> Sent: Friday, February 08, 2008 5:37 AM
> To: [EMAIL PROTECTED]
> Cc: [email protected]
> 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/[EMAIL PROTECTED]
> infoshop.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
