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]
