SELECT CASE param_name END CASE INTO @var in stored procedure using parameters to assign columns

Question

I have a stored procedure that I'm trying to create with parameters.

DELIMITER // 
    CREATE PROCEDURE insert_avg(IN area INT, IN checkid INT, IN legend INT)
this_proc: BEGIN

SELECT
    CASE legend
        WHEN 1 THEN "Input_Day"
        WHEN 2 THEN "Input_Week"
        WHEN 3 THEN "Input_Month"
        ELSE LEAVE this_proc
    END CASE;
INTO @LEGEND;

INSERT INTO 
    `input_teamboards` 
    (
        `Input_KPI`, 
        `Input_Month`, 
        `Input_Week`,   
        `Input_Day`, 
        `Input_Value`, 
        `Input_Comparison`, 
        `Input_Area`, 
        `Input_Created`
     ) 
VALUES 
    (
        7,
        IFNULL(SELECT `Input_Month` FROM `input` WHERE `check_id` = checkid, NULL, MONTH(NOW())),
        IFNULL(SELECT `Input_Week` FROM `input` WHERE `check_id` = checkid, NULL, WEEKOFYEAR(NOW())),
        IFNULL(SELECT `Input_Day` FROM `input` WHERE `check_id` = checkid, NULL, DAYOFYEAR(NOW())),
        (SELECT 
            AVG(`Input_value`) 
        FROM 
            `input` 
        WHERE 
            `Area_ID` = area AND @LEGEND = 1),
        2,
        area,
        NOW()
    )

END//
DELIMITER ;

As you can see the @LEGEND variable is being used as part of a subquery for a column name further down.

The problem I'm having is with the case statement, it keeps throwing errors within the case statement:

"#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LEAVE this_proc END CASE; INTO @LEGEND; INSERT INTO input_teamboards' at line 8"

I've used these links so far, but stuck on what I'm doing wrong?

Mysql Storing a variable with the result of an SELECT CASE
Mysql - How to quit/exit from stored procedure
case statement in stored procedure

Any help is appreciated!

No it is not a column, the columns I'm referring to are `Input_Day`, `Input_Week` and `Input_Month` — cookies, Oct 24 '18 at 07:29
You're trying to combine the usages of a `CASE` *statement* and a `CASE` *expression*. You can't arbitrarily mix and match between the two. — Damien_The_Unbeliever, Oct 24 '18 at 07:31
You can't use `@LEGEND` like this as a column name. If you want to do that, you'll have to use dynamic SQL. — Tim Biegeleisen, Oct 24 '18 at 07:33
You may want to see this for dynamic statements, [self-promotion](https://codereview.stackexchange.com/questions/205938/dynamic-statement-executor). — Nae, Oct 24 '18 at 07:34
Thank you all! I'll have a look into dynamic SQL but will be using @TimBiegeleisen ' answer for now. — cookies, Oct 24 '18 at 07:50

Tim Biegeleisen · Accepted Answer · 2018-10-24T07:43:54.130

I would rewrite your proc as this. See below the code for further explanation.

DELIMITER // 
CREATE PROCEDURE insert_avg(IN area INT, IN checkid INT, IN legend INT)
this_proc:BEGIN

IF legend NOT IN (1, 2, 3)
     LEAVE this_proc;
END IF;

INSERT INTO input_teamboards
(
    Input_KPI, 
    Input_Month, 
    Input_Week,   
    Input_Day, 
    Input_Value, 
    Input_Comparision, 
    Input_Area, 
    Input_Created
 ) 
SELECT
    7,
    COALESCE(Input_Month, MONTH(NOW())),
    COALESCE(Input_Week, WEEKOFYEAR(NOW())),
    COALESCE(Input_Day, DAYOFYEAR(NOW())),
    (SELECT AVG(Input_value) FROM input WHERE Area_ID = area AND
     ((legend = 1 AND Input_Day = 1)  OR
      (legend = 2 AND Input_Week = 1)  OR
      (legend = 3 AND Input_Month = 1))),
    2,
    area,
    NOW()
FROM input
WHERE check_id = checkid;

END//
DELIMITER ;

The major change I made here was to rewrite your insert query. I rephrased it as an INSERT INTO ... SELECT. This should work because most of your subqueries were referring to the same input table. For the average subquery, I handled the logic involving the legend parameter by just checking its value, and then checking the appropriate corresponding column. This then lets us delete the CASE expression you orignally had at the start of your proc.

The condition `IF legend < 1 OR legend > 3` should be `legend not in (1,2,3)` in case the `legend` is NULL. — slaakso, Oct 24 '18 at 07:41
That's much more straight forward, thank you @TimBiegeleisen ! — cookies, Oct 24 '18 at 07:51

SELECT CASE param_name END CASE INTO @var in stored procedure using parameters to assign columns

1 Answers1