From: Peter Brawley (peter.brawleyearthlink.net)
Date: Thu Feb 07 2008 - 14:45:06 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]