CODEWITHSUNDEEP
mysqlsqlfunctiontriggerscase
user2753441
user2753441

Reputation: 21

SQL function returning result of select case

I need to create a function that takes in parameter an id. Then I do a select case on a table where for this given id. The when statements in the case count the number of rows.

Here is my function:

CREATE function db_name.insert_stage(idStage INT)
    returns integer
    BEGIN   
        SELECT CASE
            WHEN COUNT(*) < 10 THEN 0
            WHEN COUNT(*) >= 10 and COUNT(*) <= 12 THEN 392
            WHEN COUNT(*) >= 13 and COUNT(*) <= 17 THEN 490
            ELSE 588
            END AS cost
        FROM inscriptions WHERE paid = 1 AND id_stage = idStage;
        RETURN cost;
    END

Then I use a trigger to set the cost in a field for each row already existing in stage. This trigger will also be called on insert in stage:

CREATE TRIGGER insert_stage_frais_secretariat BEFORE INSERT ON stage
FOR EACH ROW SET stage.fraisSecretariat = select anper_extranet.insert_stage(stage.id);

I don't know if the trigger is working yet, because the function isn't.

Here is what phpmyadmin tells me:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 10

I have no idea what's wrong at line 10, because it feels to me it is the when, although I tried the select statement and it works.

Upvotes: 2

Views: 2809

Answers (1)

Cristian Abelleira
Cristian Abelleira

Reputation: 355

Try this:

delimiter //
CREATE FUNCTION db_name.insert_stage(idStage INT)
RETURNS Integer
BEGIN
  DECLARE var_name INTEGER;
  SET var_name = 0;
  SELECT CASE
    WHEN COUNT(*) < 10 THEN 0
    WHEN COUNT(*) >= 10 and COUNT(*) <= 12 THEN 392
    WHEN COUNT(*) >= 13 and COUNT(*) <= 17 THEN 490
    ELSE 588
    END
into var_name
FROM inscriptions WHERE paid = 1 AND id_stage = idStage;   
 RETURN var_name;
END //

Upvotes: 2

Related Questions