CODEWITHSUNDEEP
mysqlstored-procedures
BernardA
BernardA

Reputation: 1523

how to set mysql stored procedures for update with variables

I am looking to automate a procedure I am handling manually now. So, I am new to stored procedures and the code below is not working.

It stops when it outputs the first set of variables of the SELECT statement. It does not do the UPDATE and does not continue with the WHILE loop. No errors are reported. 

DELIMITER //
CREATE PROCEDURE ins_die_1()

BEGIN

    DECLARE v_yr INT;
    DECLARE v_col CHAR(10);
    DECLARE v_base INT;
    DECLARE v_factor INT;
    DECLARE v_cf INT;
    DECLARE v_fuel CHAR(10);

    SET v_yr :=1;
    SET v_col := '';
    SET v_base := 2860;
    SET v_factor := 1;
    SET v_cf := 5;
    SET v_fuel := 'diesel';

    WHILE v_yr <= 5 DO
        SET v_col := CONCAT('ins_yr',v_yr);
        SELECT v_col, v_base, v_factor, v_fuel, v_cf;

        UPDATE ct_insurance 
            SET v_col = (v_base + 1254 + (v_factor * .0032 * price )) 
            WHERE fuel = v_fuel 
                AND CF=v_cf 
                AND price * v_factor < 162000 
                AND version_id = 1746;

        SET v_yr := v_yr+1;

        IF v_yr = 2 THEN SET v_factor := .8;
        ELSE SET v_factor := v_factor - 0.1;
        END IF;
    END WHILE; 

END//
DELIMITER ;

I know that there is the IN construct, whereby I should enter the variables manually when calling the procedures, but that seems like an awkward solution and certainly not very efficient.

In case you wonder I have many variables, it's because this is a test procedure. Once it works, hopefully, I will expand it.

Also, I am looking for a mySql solution only, ie, no php involved.

Upvotes: 0

Views: 3022

Answers (1)

Barmar
Barmar

Reputation: 780818

Column names in the UPDATE statement are taken literally, variables don't substitute there. You can do it using dynamic SQL:

SET @sql = CONCAT('UPDATE ct_insurance 
        SET ', v_col, ' = (v_base + 1254 + (v_factor * .0032 * price )) 
        WHERE fuel = v_fuel 
            AND CF=v_cf 
            AND price * v_factor < 162000 
            AND version_id = 1746');
PREPARE stmt FROM @sql;
EXECUTE stmt;

However, you can't access procedure local variables from prepared statements (see here) so you need to use user-defined @ variables.

DELIMITER //
CREATE PROCEDURE ins_die_1()

BEGIN

    DECLARE v_yr INT;
    DECLARE v_col CHAR(10);

    SET v_yr :=1;
    SET v_col := '';
    SET @v_base := 2860;
    SET @v_factor := 1;
    SET @v_cf := 5;
    SET @v_fuel := 'diesel';

    SELECT v_col, @v_base, @v_factor, @v_fuel, @v_cf;

    WHILE v_yr <= 5 DO
        SET v_col := CONCAT('ins_yr',v_yr);

        SET @sql = CONCAT('UPDATE ct_insurance 
                SET ', v_col, ' = (@v_base + 1254 + (@v_factor * .0032 * price )) 
                WHERE fuel = @v_fuel 
                    AND CF = @v_cf 
                    AND price * @v_factor < 162000 
                    AND version_id = 1746');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;

        SET v_yr := v_yr+1;

        IF v_yr = 2 THEN SET v_factor := .8;
        ELSE SET v_factor := v_factor - 0.1;
        END IF;
    END WHILE; 

END//

Upvotes: 2

Related Questions