CODEWITHSUNDEEP
mysqlsqldatabasestored-procedures
RedGiant
RedGiant

Reputation: 4748

prepare statement to pass comma-separated values to stored procedure parameter

I have trouble moving a query into a store procedure. I need to pass a comma-separated string to the parameter yr_model_no for two IN clauses. I have read a few threads and decided to use prepare statements. The original query works fine fetching multiple rows, but in the stored procedure, when I call it with call load_things('1283943kd9,2e9kk389334','53') (the first is a list of model numbers and the second is an user id), it can only fetch one single row from the first value of the comma-separate string. At first, I thought it was the length problem with the yr_model_no parameter, but changing it to varchar(200) would give me unknown column 2e9kk389334 in 'where clause' error. Can someone help me figure out what is wrong with the prepare statement or parameters in the following code?

Here's a similar fiddle example of my table schema and query.

DELIMITER ;;
CREATE PROCEDURE `load_things` (IN `yr_model_no` varchar(20), IN `yr_app_id` int(5))
BEGIN
    SET @s = 

CONCAT('
SELECT * FROM 
(
   SELECT COUNT( c.app_id ) AS users_no, ROUND( AVG( c.min ) , 1 ) AS avg_min, ROUND( AVG( c.max ) , 1 ) AS avg_max, a.mid, a.likes, a.dislikes, b.model_no
        FROM  `like` a
        RIGHT JOIN  `model` b ON a.mid = b.mid
        LEFT JOIN  `details` c ON c.mid = b.mid
        WHERE b.model_no IN (',yr_model_no,')
        GROUP BY b.model_no
)TAB1
JOIN
(
   SELECT a.app_id,b.model_no,IFNULL(c.isbooked,0) AS isbooked,d.min,d.max,e.like_type
     FROM `users` a
     JOIN `model` b
       ON b.model_no IN (',yr_model_no,')
LEFT JOIN `favorite` c 
       ON c.app_id = a.id
      AND c.mid = b.mid         
LEFT JOIN `details` d 
       ON d.app_id = a.id
      AND d.mid = b.mid 
LEFT JOIN `users_likes` e
       ON e.app_id = a.id
      AND e.mid = b.mid
    WHERE a.id = ',yr_app_id,'
)TAB2
ON TAB1.model_no = TAB2.model_no');

PREPARE stmt from @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt3;
END;;
DELIMITER ;

Upvotes: 0

Views: 3584

Answers (1)

Charleh
Charleh

Reputation: 14002

If you think about the SQL that your statement is generating it will look like this

JOIN `model` b
ON b.model_no IN (1283943kd9,2e9kk389334)

This is invalid SQL (you need quotes around the varchar values)

You need to make sure you add quotes around each value in the string that you pass to your procedure:

load_things('''1283943kd9'',''2e9kk389334''','53')

Not sure if this is the correct way to escape strings in MySQL (you might need to look at the docs)

This will result in:

JOIN `model` b
ON b.model_no IN ('1283943kd9','2e9kk389334')

Upvotes: 1

Related Questions