From: Peter Brawley (peter.brawleyearthlink.net)
Date: Thu Feb 07 2008 - 10:10:25 CST

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

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

>

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