IN Clause dont work in MySQL for me

Question

I am passing my parameter as 'Suburbun','Indigo' to retrieve records matching both Campaigns in below Stored Procedure created in MySql.

CREATE PROCEDURE `DemoSP`(Campaign VARCHAR(3000))
BEGIN
    SET @query = CONCAT('Select * from vicidial_log WHERE campaign_id IN (?)');
  PREPARE stmt FROM @query;
  SET @CampaignID = Campaign;
  EXECUTE stmt USING @CampaignID;
  DEALLOCATE PREPARE stmt;
END;

It Doesn't give any rows!

But when i pass only 'Suburbun' in SP, it gives 6 Rows!

Where am i going wrong?

--Answer !

I tried as Lee Fentress commented in http://www.poolofthought.com/index.php/2008/12/28/a-comma-seperated-list-as-parameter-to-mysql-stored-procedure/ and peterm answer reflected similar coding,

It worked!

Thanks, but i find this negative mark as compared to SQL Server.

Gee, Thank you Guys!!

score 0 · Answer 1 · answered Jan 04 '14 at 08:53

0

Try this:

There is no need to use PREPARE STATEMENT. You can get the result using FIND_IN_SET() function

SELECT * FROM vicidial_log WHERE FIND_IN_SET(campaign_id, Campaign)

answered Jan 04 '14 at 08:53

Saharsh Shah

28,687
8
48
83

@Akash May I know the issue you're facing? – Saharsh Shah Jan 04 '14 at 09:20
It just didnt worked! My Statement Select * from vicidial_closer_log WHERE FIND_IN_SET(campaign_id, Campaign) didnt returned any row – Akash Jan 04 '14 at 09:33
In order for `FIND_IN_SET()` to work delimited values in `Campaign` parameter should be unquoted `'Suburbun,Indigo'`. But the **major** issue with this approach is that it prevents any index from being used effectively causing a full scan every time. – peterm Jan 04 '14 at 09:52

peterm · Accepted Answer · 2014-01-04T09:28:37.597

0

You won't be able to use USING in this case. You can just build the full query sting and execute it without parameters

DELIMITER $$
CREATE PROCEDURE DemoSP(Campaign VARCHAR(3000))
BEGIN
  SET @query = CONCAT('SELECT * FROM vicidial_log WHERE campaign_id IN (', Campaign, ')');
  PREPARE stmt FROM @query;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

Note: make sure that delimited values that you pass in Campaign are properly quoted (like you said they are) and quotes in values, if there is any, are escaped.

Here is SQLFiddle demo

edited Jan 04 '14 at 09:28

answered Jan 04 '14 at 09:20

peterm

91,357
15
148
157

Hey, Peter thanks!!! It Worked. But i came across similar thing(See ,y updated Question). I tried and it worked! And Thanks a lot for SQL Fiddle!! Helped me alot! – Akash Jan 04 '14 at 09:31

score -1 · Answer 3 · answered Jan 04 '14 at 08:51

-1

try this

"Select * from vicidial_log WHERE campaign_id IN ('?')"

instead of

'Select * from vicidial_log WHERE campaign_id IN (?)'

answered Jan 04 '14 at 08:51

Satish Sharma

9,547
6
29
51

This won't work - go ahead and try it. Prepared statements don't support variable arrays – mvp Jan 04 '14 at 09:00

IN Clause dont work in MySQL for me

3 Answers3