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

Zohar Peled · Accepted Answer · 2020-10-06T07:16:45.393

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;

edited Oct 06 '20 at 07:16

answered Oct 06 '20 at 07:10

Zohar Peled

79,642
10
69
121

The varchar(max) is used currently as I haven't get determined the best length-size. For the question I shortend some of the @SpecialsVariable values. – Wikus Oct 06 '20 at 07:34
BTW, you really shouldn't be using delimited strings in SQL. ([Read this](https://stackoverflow.com/a/3653574/3094533)) – Zohar Peled Oct 06 '20 at 07:36

score 0 · Answer 2 · answered Oct 06 '20 at 07:11

I don't believe you can combine them like that.

I think your options are

Set the variables independently, like your first block
Nested if statements
Create a reference table

Example for Nested if

IF @Special = 'Petr_analysis' 
    BEGIN 
    (set your variables) 
    END 
ELSE IF @Special = 'AFT_analysis'
    BEGIN
    ...

Example of reference table with the three columns e.g., Special, SpecialsVariable, SpecialName

SELECT  @SpecialVariable = yt.SpecialVariable, 
        @SpecialName = yt.SpecialName 
FROM    yourtable yt 
WHERE   yt.Special = @Special

score 0 · Answer 3 · answered Oct 07 '20 at 07:25

If this is a once off kind of requirement, another alternative might be to use table value constructors:

e.g.:

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

select @SpecialsVariable = tab.Variable, @SpecialName = tab.Name from
    (VALUES 
         ('Petr_analysis', 'RAW_ColloTel,RAW_DetroCol',  'Petrography'),
         ('AFT_analysis', 'RAW_Defo,RAW_Defr,RAW_Floo,RAW_Flor', 'Ash Fusion Temperature'),
         ('Fisch_analysis', 'RAW_COke,RAW_Gas,RAW_H2O,RAW_Tar', 'Fischer-Tropsch')         
    ) tab (Special, Variable, Name) 
    where tab.Special = @Special

Info on Table Value Constructors:

https://learn.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql?view=sql-server-ver15

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

3 Answers3