MySql - How to assign different aliases to a single select column, determined by a case statement

Question

I have this following working code (MySql 8.0):

    select user_id as "user",
        (case when userBad then CodeRed 
        else CodeBlue
        end)
    From colours

I want to assign an alias Red for CodeRed column, and Blue for CodeBlue column. I know I can't give an alias within the case statement. So what would be the correct syntax to assigning a condition alias? I thought perhaps I declare a string variable to hold the value of the alias and then append the variable to the case statement:

declare alias varchar(4);
if userBad then set alias = "Red"
else set alias = "Blue";
select user_id as "user",
       (case when userBad then CodeRed 
       else CodeBlue
       end) as alias
From colours;

But as you probably expected, I end up with a column called alias! So is there a way to assign conditional aliases based on case select? Thanks

UPDATE: Slava has provided a great solution using a prepared statement to concatinate the alias variable. My Full code: I now currently get an error 1054: Unknown column userBad in 'field list' but I am confident Slava has put me on the right track.

PROCEDURE `Test`(userBad bool)
BEGIN
    declare alias varchar(4);    

    drop temporary table if exists colours;
    create temporary table colours (user_id int, codeRed int, codeBlue int);
    

    if userBad then set alias = "Red";
    else set alias = "Blue";
    end if;
    
    SET @sql = CONCAT('select user_id as "user",
           (case when userBad = true then codeRed 
           else codeBlue
           end) as ', alias, ' from colours');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

FINAL UPDATE: I have solved the "1054: Unknown column userBad in 'field list'" error. I posted the full working solution below. My thanks to Slava in putting me on the right track of using prepared statements.

Answer 1

I want to thank Slava for putting me on the right track with using prepared statements. It was a close solution that didn't quite work and other solutions were not quite the required results.

A prepared statement was the solution but there was an issue in that the variable userBad was rejected. What I realise is we can't have variables inside the prepared statement but we can have a placeholder value, represented by ? then execute the statement USING userBad. However, that still gives errors because the USING clause of executing a prepared statement requires a user/session variable, not a local variable. Therefore need to assign local variable userBad to a session variable @uB. The full working solution is as follows:

PROCEDURE `Test`(userBad int)
BEGIN
    declare alias varchar(4);


    drop temporary table if exists colours;
    create temporary table colours (user_id int, codeRed int, codeBlue int);
    

    if userBad then set alias = "Red";
    else set alias = "Blue";
    end if;

    set @uB = userBad;
    SET @sql = CONCAT('select user_id as "user",
           (case when ? then codeRed 
           else codeBlue
           end) as ', alias, ' from colours');
    PREPARE stmt FROM @sql;
    EXECUTE stmt using @uB;
    DEALLOCATE PREPARE stmt
END

One more thing to add, for those of you looking for such a solution, if you have more than one session variable, you need to list those variables out in the sequence used in the statement.