Mamof Mamof - 1 month ago 14
MySQL Question

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

Answer

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