mysql function return list of data

Question

I would like to write a mysql function that takes an integer as input and that function execute a select statements "select id from table_name" then i want that function return the result of select statements.

then

in query i want to do smthing like:

select id,name from table_name where id in (call function here that returns a list of ids).

Is that valid in mysql ??

Thanks in Advance

Answer 1

No, you cannot do this in MySQL.

A MySQL procedure that returns a resultset cannot be used in a subquery.

If your function is just a number of SELECT statements, you can do something like this instead:

SELECT  id, name
FROM    mytable1
WHERE   id IN
        (
        SELECT  id
        FROM    mytable2
        WHERE   value = @myinteger
        UNION ALL
        SELECT  id
        FROM    mytable2
        WHERE   value = @myinteger
        UNION ALL
        …
        )

or, which is more efficient,

SELECT  id, name
FROM    mytable1 t1
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    mytable2 t2
        WHERE   t2.id = t1.id
                AND t2.value = @myinteger
        UNION ALL
        SELECT  NULL
        FROM    mytable3 t2
        WHERE   t3.id = t1.id
                AND t3.value = @myinteger
        UNION ALL
        …
        )