>...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
>the request fast, and the concatenating and string handling takes some
of that away.
Unfortunately MySQL sprocs do not yet deliver this advantage.
Magne Westlie wrote:
> 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?
> Peter Brawley wrote:
>> 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
>> DROP PROCEDURE IF EXISTS passInParam;
>> DELIMITER |
>> CREATE PROCEDURE passInParam( IN qry VARCHAR(100), IN param
>> VARCHAR(1000) )
>> SET @qry = CONCAT( qry, param, ')' );
>> PREPARE stmt FROM @qry;
>> EXECUTE stmt;
>> DROP PREPARE stmt;
>> 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''' ); |