CODEWITHSUNDEEP
mysqlsqlstored-procedures
Jaquarh
Jaquarh

Reputation: 6673

How to CONCAT a update query in a procedure

I am trying to create a procedure to update a row when a bug gets fixed. Here is what it looks like:

DELIMITER $$
CREATE PROCEDURE bugFix (
    IN rid_in INT,
    IN fix_details_in VARCHAR (300)
) BEGIN
    SET @resolved_qry = CONCAT('UPDATE _bug_report SET resolved = 1 WHERE rid = ', rid_in);
    PREPARE stmt FROM @resolved_qry;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    UPDATE _bug_report SET fix_details = CONCAT(fix_details, fix_details_in) WHERE rid = CONCAT(rid, rid_in);

END $$
DELIMITER ;

When changing the resolved type to 1:

SET @resolved_qry = CONCAT('UPDATE _bug_report SET resolved = 1 WHERE rid = ', rid_in);
PREPARE stmt FROM @resolved_qry;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

It works fine, however when updating the fix_details column to include the details it does not work:

UPDATE _bug_report SET fix_details = CONCAT(fix_details, fix_details_in) WHERE rid = CONCAT(rid, rid_in);

Here is how I call it:

CALL bugFix(1, 'Hey, we fixed it');

Any ideas how to do this to use the fix_details_in value?

Upvotes: 0

Views: 105

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Why are you using dynamic SQL?

DELIMITER $$
CREATE PROCEDURE bugFix (
    IN rid_in INT,
    IN fix_details_in VARCHAR (300)
) BEGIN
    UPDATE _bug_report
        SET resolved = 1,
            fix_details = CONCAT(fix_details, fix_details_in)
        WHERE rid = rid_in;
END $$
DELIMITER ;

Presumably, "not work" means that fix_details is not being updated. That would be because nothing matches:

WHERE rid = CONCAT(rid, rid_in)

Given that rid_in is an integer, this will always fail.

Upvotes: 1

Related Questions