MariaDB 10.4

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE PROCEDURE IF NOT EXISTS `play_dices`(INOUT attack INT, INOUT defense INT)
BEGIN
    DECLARE var_dice_1_attack INT;
    DECLARE var_dice_2_attack INT;
    DECLARE var_dice_3_attack INT;
    DECLARE var_dice_1_defense INT;
    DECLARE var_dice_2_defense INT;
    DECLARE var_dice_3_defense INT;
    
    CREATE TEMPORARY TABLE attack_table (dice INT NOT NULL);
    CREATE TEMPORARY TABLE defense_table (dice INT NOT NULL);
    
    -- FLOOR(RAND() * (<max> - <min> + 1)) + <min>
    -- generates a number between <min> and <max> inclusive 
    CASE attack
        WHEN '1' THEN
            SET var_dice_1_attack = FLOOR(RAND() * 6) + 1;
            INSERT INTO attack_table (dice) VALUE (var_dice_1_attack);
        WHEN '2' THEN
            SET var_dice_1_attack = FLOOR(RAND() * 6) + 1;
            SET var_dice_2_attack = FLOOR(RAND() * 6) + 1;
            INSERT INTO attack_table (dice) VALUE (var_dice_1_attack);
            INSERT INTO attack_table (dice) VALUE (var_dice_2_attack);
        WHEN '3' THEN
            SET var_dice_1_attack = FLOOR(RAND() * 6) + 1;
            SET var_dice_2_attack = FLOOR(RAND() * 6) + 1;
            SET var_dice_3_attack = FLOOR(RAND() * 6) + 1;
            INSERT INTO attack_table (dice) VALUE (var_dice_1_attack);
            INSERT INTO attack_table (dice) VALUE (var_dice_2_attack);
            INSERT INTO attack_table (dice) VALUE (var_dice_3_attack);
    END CASE;
    
    -- REMOVE THOSE SELECTS
    SELECT 'BEFORE'; 
    SELECT var_dice_1_attack;
    SELECT var_dice_2_attack;
    SELECT var_dice_3_attack;
    
    ALTER TABLE attack_table ORDER BY dice DESC;
    
    CASE attack
        WHEN '1' THEN
            SET var_dice_1_attack = (SELECT dice FROM attack_table  limit 1);
        WHEN '2' THEN
            SET var_dice_1_attack = (SELECT dice FROM attack_table  limit 1);
            SET var_dice_2_attack = (SELECT dice FROM attack_table  limit 1,1);
        WHEN '3' THEN
            SET var_dice_1_attack = (SELECT dice FROM attack_table  limit 1);
            SET var_dice_2_attack = (SELECT dice FROM attack_table  limit 1,1);
            SET var_dice_3_attack = (SELECT dice FROM attack_table  limit 2,1);
    END CASE;
    
    
    -- REMOVE THOSE SELECTS
    SELECT 'AFTER';
    SELECT var_dice_1_attack;
    SELECT var_dice_2_attack;
    SELECT var_dice_3_attack;

END;

BEFORE
BEFORE

var_dice_1_attack
1

var_dice_2_attack
4

var_dice_3_attack
null

AFTER
AFTER

var_dice_1_attack
4

var_dice_2_attack
1

var_dice_3_attack
null