16

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.

10

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');
  • 1
    Whats's the point of CONCAT in the code above? – Pacerier Apr 2 '15 at 11:25
  • @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'); – Deckard Jun 14 '17 at 7:39
9

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');
0

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
  • 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. – Juanal Sep 27 at 7:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.