Re: How to treat a parameter as array in stored procedure?

[William Chiquito]

Hi Cong,

Try:

DROP TABLE IF EXISTS `mytable`;

CREATE TABLE `mytable` (
  `revision` int(11) default NULL,
  `name` varchar(20) default NULL,
  `path` varchar(20) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `mytable` */

insert  into `mytable`(`revision`,`name`,`path`) values (1,'file1.h','dir1'),(2,'file2.h','dir2');

/*Table structure for table `mytable1` */

DROP TABLE IF EXISTS `mytable1`;

CREATE TABLE `mytable1` (
  `name` varchar(20) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `mytable1` */

insert  into `mytable1`(`name`) values ('file1.h'),('file2.h'),('file3.h');

A option is:

/*!50003 DROP PROCEDURE IF EXISTS  `mysp` */;

DELIMITER $$

/*!50003 CREATE PROCEDURE `mysp`()
BEGIN
	SELECT * FROM mytable WHERE name IN (SELECT name FROM mytable1);
END */$$
DELIMITER ;

Another option is:

/*!50003 DROP PROCEDURE IF EXISTS  `mysp` */;

DELIMITER $$

/*!50003 CREATE PROCEDURE `mysp`()
BEGIN
	DECLARE parameter VARCHAR(4000);
	SELECT IFNULL(CONCAT('''', GROUP_CONCAT(name SEPARATOR ''', '''), ''''), 'NULL') INTO parameter FROM mytable1;
	SET @qry = CONCAT('SELECT * FROM mytable WHERE name IN (', parameter, ')');	
	PREPARE stmt FROM @qry;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END */$$
DELIMITER ;

Edited 1 time(s). Last edit at 06/05/2007 07:45PM by William Chiquito.