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.