How do I create a MySQL function to output a query that has values from 1 to a given number?

Question

I am bit new to MySQL. I am trying to create a mysql function that will produce and output a query with a single column with values from 1 to that given number.

Start of the function looks like,

create function get_NumberSequence (n)

Suppose I call the function as,

select get_NumberSequence(5);

I should get the output like below.

Sequence
--------
  1 
  2 
  3
  4
  5

I tried an approach to combine and store queries like,

@num:=(select 1 as Sequence)

@num:=@num union (select 2 as Sequence)

This didn't work for me.

It Seems MySql functions doen't provide capability of returning a query results. To return query result need to create a procedure. — M.E.I. De Silva, Dec 20 '16 at 07:05
For MySQL procedures look here [link](http://stackoverflow.com/questions/15786240/mysql-create-stored-procedure-syntax-with-delimiter) — M.E.I. De Silva, Dec 20 '16 at 07:06

score 0 · Accepted Answer · answered Dec 18 '16 at 08:42

This is for case that your input number l_input_number < 10^4 (10000).

You could change your query with larger input number

SELECT @row := @row + 1 as sequence 
FROM 
    (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t,
    (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
    (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t3, 
    (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t4, 
    (SELECT @row:=0) t_init
LIMIT 17;  --LIMIT l_input_number;

And use IF ELSE STATEMENT for each query of input number range for better performance

IF l_input_number < 1000 
THEN --only use query up to t3 
ELSEIF l_input_number < 10000 
THEN --use query up to t4 
ELSEIF l_input_number < 100000 
THEN --use query up to t5 
--etc........
END IF;

How do I create a MySQL function to output a query that has values from 1 to a given number?

1 Answers1