CODEWITHSUNDEEP
mysqldatabasestored-procedurestriggers
Becerra95
Becerra95

Reputation: 43

How to set a variable to the result of a stored procedure inside a Trigger MYSQL?

I have a little problem here, I'm making a trigger for my DB work, but I don't know how to use a stored procedure inside a trigger, I want to save the result of the procedure in a variable and then use the variable later on an IF comparator, this is my code:

DELIMITER @
CREATE TRIGGER insert_players AFTER INSERT ON players FOR EACH ROW
BEGIN
DECLARE A varchar(50);
set A = CALL consult_sex(player_name); //I WANT SOMETHING LIKE THIS
IF A != FEMALE THEN
INSERT INTO males (id_player, player_fullname, player_celphone)
VALUES (new.id_player, concat(new.player_name, " ", new.player_lastname), new.player_phone);
END IF; 
END;
@

Could someone help me with this doubt? Would be wonderfull, thanks.

Upvotes: 4

Views: 14650

Answers (2)

Zach Victor
Zach Victor

Reputation: 469

If you want a stored routine to return a value, then you need a stored function not a stored procedure. Using a stored function, the statement would be as follows:

SET A = consult_sex(player_name);

This is an dummy function:

CREATE FUNCTION `consult_sex`(player_name VARCHAR(100)) RETURNS VARCHAR(6) CHARSET utf8
BEGIN

    DECLARE v_sex VARCHAR(6);

    -- Randomly generate one of two values, MALE or FEMALE
    SET v_sex = (SELECT CASE FLOOR(1 + RAND() * (3-1)) WHEN 1 THEN 'MALE' ELSE 'FEMALE' END);

    RETURN v_sex;
END

The trigger code you provided has some syntax errors. Given that the table and column names are correct, this would work with a stored function:

DELIMITER @
CREATE TRIGGER insert_players AFTER INSERT ON players FOR EACH ROW
BEGIN
    DECLARE A VARCHAR(50);
    SET A = consult_sex(NEW.player_name); 
    IF A != 'FEMALE' THEN
        INSERT INTO males (id_player, player_fullname, player_celphone)
        VALUES (NEW.id_player, CONCAT(NEW.player_name, ' ', NEW.player_lastname), NEW.player_phone);
    END IF; 
END;
@

Please be sure to double-check the spelling of the player_celphone column.

Upvotes: 2

Rahul
Rahul

Reputation: 77896

have your procedure take a OUT parameter like

CREATE PROCEDURE consult_sex(player_name VARCHAR(20), OUT player_sex VARCHAR(10))

In your procedure SET the value like

SET player_sex = <your query to get sex>

Call your procedure passing a parameter 

call consult_sex(player_name, @out_value);
select @out_value;

Upvotes: 5

Related Questions