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
>
>
>>
>
