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
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
…
)
Yes you can use subqueries in MySql
something like following
select id,name from table_name1
where id in (select DISTINCT(table_name1_id) from table_name2)
You dont need a function, you can use a subquery.
E.g.
select id,name from table_name where id in (select someIds from othertable)
Sure. You can use a Stored Procedure, a UDF or even a subquery.
Have a look:
If you really need to use a function, you can:
CREATE FUNCTION my_func (in_id INT(11), value INT(11)) RETURNS INT(11) DETERMINISTIC
RETURN in_id IN (SELECT id FROM table_name WHERE id = value);
and you would call it with this:
SELECT id,name from table_name where myfunc(id, 1);
It is generally slow because MySQL can't optimize its use, but does the job.
