How to use prepared statements within MySQL stored procedures?

Question

I'm using mysql and I need to somehow use the column curid returned by the prepared statement in the later query. I use prepared statements because as I've read, it is the only way to pass a variable to the LIMIT clause. I have this stored procedure here:

DROP PROCEDURE IF EXISTS fixbalance;
CREATE PROCEDURE fixbalance (userid INT)
 BEGIN
 DECLARE i INT DEFAULT 0;
 DECLARE balance INT DEFAULT 0;
 DECLARE idcnt INT;
 SET idcnt = (SELECT COALESCE(COUNT(id), 0) 
 FROM coupon_operations 
 WHERE user_id = userid);
 IF idcnt <> 0 THEN
 WHILE i <= idcnt DO
 BEGIN
 SET @iter = i;
 SET @user_id = userid; 
 SET @sql = CONCAT('SELECT id AS curid 
 FROM coupon_operations 
 WHERE user_id = ? 
 ORDER BY id ASC 
 LIMIT ?, 1');
 PREPARE stmt FROM @sql;
 EXECUTE stmt USING @user_id, @iter;
 DEALLOCATE PREPARE stmt;
 SET balance = balance + (SELECT points 
 FROM coupon_operations 
 WHERE user_id = @user_id 
 AND id = @curid);
 UPDATE coupon_operations SET balance = balance;
 SET i = i + 1;
 END;
 END WHILE;
 END IF;
END;
|

This does not work - I'm not sure how to pass the curid.

Answer 1

The solution was to SET the variable in the prepared statement itself as in:

SET @sql = CONCAT('SET @curid = SELECT id
 FROM coupon_operations 
 WHERE user_id = ? 
 ORDER BY id ASC 
 LIMIT ?, 1');

• Pacerier: Whats's the point of CONCAT in the code above?
• Deckard: @Pacerier You don't need to use CONCAT unless you pass the table name or something as a parameter. ex) CONCAT('select * from ', the_table_name, ' where id>100');

Answer 2

I'm glad you found your answer. Another solution would be to use the SELECT...INTO syntax:

SET @sql = CONCAT('SELECT id INTO @curid FROM coupon_operations 
 WHERE user_id = ? 
 ORDER BY id ASC 
 LIMIT ?, 1');

Answer 3

Please tried this. to solve Concatenate, PREPARE and EXECUTE statements as bellow..

CREATE DEFINER=`products`@`localhost` PROCEDURE `generateMeritList`(
 IN `mastercategory_id` INT(11), 
 IN `masterschools_id` INT(11)
 )
 NO SQL
 begin
 declare total int default 0;
 declare conditions varchar(255) default ''; 
 declare finalQuery varchar(60000) default '';
if mastercategory_id > 0 THEN
 SET conditions = CONCAT(' and app.masterschools_id = ', mastercategory_id);
end if;
SET @finalQuery = CONCAT(
 "SELECT * FROM applications app INNER JOIN masterschools school ON school.id = app.masterschools_id
WHERE 
 (app.status = 'active'", conditions, " LIMIT ",total); 
 PREPARE stmt FROM @finalQuery;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
end

• Juanal: This particular example is fine but I'd discourage using it as a general pattern due to risk of SQL injection. If the input was a string - such as VARCHAR - instead of INT, that'd potentially allow an attacker to inject SQL code. A properly-created prepared statement is safer than concatenating strings with external input.