How do I set a local variable using a CASE statement

Question

Let me start by saying "I'm sure there's an easier way to do this but I can't find it..."

I'm trying to construct a tracking number based on 4 separate values in a record so I can concatenate the variables and insert that concatenated string into another table.

DECLARE @fy char = '';
SET @fy = (SELECT proposalCallID,
                CASE
                    WHEN proposalCallID IN (7, 8) THEN '19'
                    WHEN proposalCallID IN (5, 6) THEN '18'
                END
            FROM proposalPackage WHERE proposalCallID = 15)

I get an error "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

There are 4 parts to the tracking number so I have 4 of these queries a need to concatenate the parts to make the full tracking number.

Any help is appreciated.

Answer 1

Is this what you are trying to do?

DECLARE @fy char(2) = '';

SELECT @fy = (CASE WHEN proposalCallID IN (7, 8) THEN '19'
                   WHEN proposalCallID IN (5, 6) THEN '18'
              END)
FROM proposalPackage
WHERE proposalCallID = 15;

Note the length specification for @fy, so the value fits. Always use length specification with character types in SQL Server.

Given the logic, you can dispense with setting the value entirely. The value returned by the CASE expression is NULL, so:

DECLARE @fy char(2);

Has exactly the same effect.