How to execute SELECT from List SQL query inside the MYSQL function?

Question

I have created a function in MYSQL as follow

CREATE FUNCTION `getTaskIds` ( typeIds TEXT) RETURNS TEXT
BEGIN
DECLARE output TEXT;
SELECT GROUP_CONCAT(id) INTO output FROM task WHERE task_id IN (typeIds );
RETURN output;
END

when I execute the function like SELECT getTaskIds( '1,2,3' ), It gives me the result for only task_id = 1. It does not condider task_id 2 and 3.

What am I missing in the function? Can you guys help me out?

Thanx in advance.

You might look at alternatives to passing a list of keys as a parameter, e.g. [here](http://stackoverflow.com/questions/1524858/create-table-variable-in-mysql) — StuartLC, Oct 21 '14 at 13:13
At present, you will need to use dynamic sql (e.g. [prepared statemt](http://stackoverflow.com/a/5728155/314291) ) to 'append' the list of `task_ids` to be retrieved. Unfortunately, MySql doesn't support Table Valued parameters (SqlServer) or Arrays (Oracle) either, leaving a further alternative of using Temporary tables on the connection. [More here](http://stackoverflow.com/questions/4155873/find-in-set-vs-in) — StuartLC, Oct 21 '14 at 13:17

score 2 · Accepted Answer · answered Oct 21 '14 at 13:18

2

You can't use IN since you are passing in a string. Try this FIND_IN_SET():

 SELECT GROUP_CONCAT(id) INTO output FROM task WHERE FIND_IN_SET(task_id, typeIds) > 0;

answered Oct 21 '14 at 13:18

Dan

876
5
15

score 0 · Answer 2 · answered Oct 21 '14 at 13:16

0

You need to pass all parameters in the function. So if you want 3 parameters:

CREATE FUNCTION `getTaskIds` ( typeId1 TEXT, typeId2 TEXT, typeId3 TEXT) RETURNS TEXT
BEGIN
DECLARE output TEXT;
// do what you want to do
RETURN output;
END

answered Oct 21 '14 at 13:16

Refilon

3,334
1
27
51

How to execute SELECT from List SQL query inside the MYSQL function?

2 Answers2