Q:

MySQL function for count looping

We're migrating from FirebirdSQL to MySQL and we have a stored function that takes an int value and will produce that number of rows. In converting it to a MySQL function it's returning the ending increment number instead of the rows.
Firebird
   SET TERM ^ ;   
    RECREATE PROCEDURE CREATELIST (  NUM INTEGER) RETURNS (  CNT INTEGER)
    AS  BEGIN 
        Cnt = 1; 
        WHILE (Cnt <= num) DO 
          BEGIN SUSPEND; /* Return next line */ 
          Cnt = Cnt + 1; END END^

    SET TERM ; ^

Running the function: SELECT CreateList(5)
Results in:
1
2
3
4
5
MySQL
DELIMITER ^ CREATE FUNCTION CreateList(num INT) RETURNS INT 
BEGIN   
   DECLARE cnt INT; 
   SET cnt = 1;    
   sloop:LOOP       
     if (cnt <= num) THEN
       SET cnt = cnt +1;        
     ELSE           
       LEAVE sloop;         
     END IF;    
   END LOOP;
   RETURN cnt; 
END ^ DELIMITER ;

Running the function: SELECT CreateList(5)
Results in:
6

A:

To get a table in MySQL you must use stored procedure :
DELIMITER ^ 
PROCEDURE `CreateList`(IN `num` INT)
BEGIN
    DECLARE cnt INT; 
    SET cnt = 1;
    create TEMPORARY table t (i int);
    sloop:LOOP 
        if (cnt <= num) 
            THEN SET cnt = cnt +1;
            insert into t value(cnt);
        ELSE LEAVE sloop;
        END IF;

    END LOOP;
    select * from t;
END

