salix/db/routines/vn/procedures/client_RandomList.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` PROCEDURE `vn`.`client_RandomList`(vNumber INT)
BEGIN

	DECLARE i INT DEFAULT 0;
    DECLARE c INT DEFAULT 0;
    DECLARE maxClientFk INT;
         
    DROP TEMPORARY TABLE IF EXISTS tmp.client;
    
    CREATE TEMPORARY TABLE tmp.`client`
    (id INT AUTO_INCREMENT,
     clientFk INT,
     isSelected TINYINT(1) NOT NULL DEFAULT 0,
     PRIMARY KEY(id),
     UNIQUE KEY clientFk (clientFk))
     ENGINE = MEMORY;
     
     INSERT INTO tmp.client(clientFk)
		SELECT DISTINCT clientFk
			FROM vn.invoiceOut
			WHERE issued > TIMESTAMPADD(MONTH, -2, util.VN_CURDATE());
            
	SELECT max(id) INTO maxClientFk
		FROM tmp.client;
     
    
	WHILE i < vNumber DO
    
		SET i = i + 1;
   
        WHILE c = 0 DO
        
			SELECT id INTO c
				FROM tmp.client
				WHERE id = floor(RAND() * maxClientFk) + 1
					AND isSelected = FALSE
                    LIMIT 1;
			
		END WHILE;
        
       -- SELECT i, maxClientFk, c;
        
        UPDATE tmp.client 
			SET isSelected = TRUE 
            WHERE id = c;
        
        SET c = 0;
    
    END WHILE;
    
    SELECT c.id, c.name FROM tmp.client tc
		JOIN vn.client c ON c.id = tc.clientFk
		WHERE isSelected
        ORDER BY clientFk;


END$$
DELIMITER ;