Way to combine two case expressions that populate two separate variables?

Question

I have a stored procedure that takes user inputs and then returns two values. Currently I have 2 case-when expressions that do the job for me, but I am trying to figure out if I can combine the 2 expressions to have simpler, neater code.

Below is my current case-when code

DECLARE @SpecialsVariable varchar(max)
DECLARE @SpecialName varchar(64) 

SET @SpecialsVariable = CASE @Special
                           WHEN 'Petr_analysis' THEN 'RAW_ColloTel,RAW_DetroCol'
                           WHEN 'AFT_analysis' THEN 'RAW_Defo,RAW_Defr,RAW_Floo,RAW_Flor'
                           WHEN 'Fisch_analysis' THEN 'RAW_COke,RAW_Gas,RAW_H2O,RAW_Tar'
                        END

SET @SpecialName =  CASE @Special
                       WHEN 'Petr_analysis' THEN 'Petrography'
                       WHEN 'AFT_analysis' THEN 'Ash Fusion Temperature'
                       WHEN 'Fisch_analysis' THEN 'Fischer-Tropsch'
                    END

I tried the code below but I get an error regarding the comma between the 2 set variables

DECLARE @Special varchar(64) = 'Petr_analysis'
DECLARE @SpecialsVariable varchar(max)
DECLARE @SpecialName varchar(64) 

set @SpecialName, @SpecialsVariable =  case @Special
    when 'Petr_analysis' then 'Petrography', 'RAW_ColloTel,RAW_DetroCol'
    when 'AFT_analysis' then 'Ash Fusion Temperature', 'RAW_Defo,RAW_Defr,RAW_Floo,RAW_Flor'
    when 'Fisch_analysis' then 'Fischer-Tropsch', 'RAW_COke,RAW_Gas,RAW_H2O,RAW_Tar'
end

Thank you in advance

Answer 1

No, you can't.
A case expression can only return a scalar value for each condition.

What you can do, however, is use a table and populate your variables using a select statement:

CREATE TABLE SpecialMapping -- Find a better name for this, please
(
    Special varchar(64),
    Name varchar(64), 
    Variable varchar(max) -- Do you really need max here? 
)

INSERT INTO SpecialMapping (Sepcial, Name, Variable) VALUES
('Petr_analysis', 'Petrography', 'RAW_ColloTel,RAW_DetroCol'),
('AFT_analysis', 'Ash Fusion Temperature', 'RAW_Defo,RAW_Defr,RAW_Floo,RAW_Flor'),
('Fisch_analysis', 'Fischer-Tropsch', 'RAW_COke,RAW_Gas,RAW_H2O,RAW_Tar');

and in the stored procedure:

SELECT @SpecialName = Name
     , @SpecialsVariable = Variable 
FROM SpecialMapping
WHERE WHERE Special = @Special;