Re: Any idea about this procedure?

[William Chiquito]

Hi Borja,

Try:

DROP TABLE IF EXISTS `t1`;

CREATE TABLE `t1` (
  `name` varchar(50) collate latin1_spanish_ci default NULL,
  `surname` varchar(50) collate latin1_spanish_ci default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

insert  into `t1`(`name`,`surname`) values ('Joe','Smith');
insert  into `t1`(`name`,`surname`) values ('Mike','Johnson');
insert  into `t1`(`name`,`surname`) values ('John','Street');
insert  into `t1`(`name`,`surname`) values ('Jonathan','Jonathan');

DELIMITER $$

DROP PROCEDURE IF EXISTS `mytest`$$

CREATE PROCEDURE `mytest`()
BEGIN
	DECLARE done INT DEFAULT 0;
	DECLARE a CHAR(2);
	DECLARE cur1 CURSOR FOR SELECT (SUBSTRING(LOWER(name), 1, 2)) AS m FROM t1 GROUP BY m;
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
	OPEN cur1;
	REPEAT
		FETCH cur1 INTO a;
		IF NOT done THEN
			SET @qry = CONCAT('SELECT CONCAT(name, '' '', surname) INTO OUTFILE ''z:\\\\', a, '.txt''
                        LINES TERMINATED BY ''\n'' FROM t1 WHERE name REGEXP ''^', a, '''');
			PREPARE stmt FROM @qry;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;
		END IF;
	UNTIL done END REPEAT;
END$$

DELIMITER ;

Edited 4 time(s). Last edit at 07/02/2007 08:43AM by William Chiquito.