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 — StuartLC
– StuartLC, Commented Oct 21, 2014 at 13:13
At present, you will need to use dynamic sql (e.g. prepared statemt ) 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 — StuartLC
– StuartLC, Commented Oct 21, 2014 at 13:17

Dan · Accepted Answer · 2014-10-21 13:18:06Z

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, 2014 at 13:18

Dan

8765 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Refilon · Accepted Answer · 2014-10-21 13:16:48Z

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, 2014 at 13:16

Refilon

3,4971 gold badge33 silver badges54 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related