Refer to original value within CASE statement

Question

I'm wondering whether a variable exists within a case statement that you can use to refer back to the value that was passed in.

For instance, if you have a table like this:

Department
shipping
receiving
hr

And want this output:

Shipping
Receiving 
HR

Here's a query that does it:

DECLARE @table TABLE (dept VARCHAR(25))
INSERT INTO @table VALUES ('shipping'), ('receiving'), ('hr')
SELECT 
    CASE UPPER(SUBSTRING(dept, 1, 1)) + LOWER(SUBSTRING(dept, 2, LEN(dept) - 1)) 
        WHEN 'Hr' THEN 'HR' 
    ELSE UPPER(SUBSTRING(dept, 1, 1)) + LOWER(SUBSTRING(dept, 2, LEN(dept) - 1))
    END
FROM @table

Is there a way to do it without repeating all the functions in the else statement? In the ELSE, I just want it to return the original value I passed into the case statement. I'm looking for some built-in variable for the original value, conceptually like this:

ELSE ORIGINAL_VALUE

Answer 1

You can use apply with values :

SELECT t.*, (CASE WHEN ORIGINAL_VALUE = 'Hr' THEN 'HR' ELSE ORIGINAL_VALUE 
             END)
FROM @table t CROSS APPLY
     ( VALUES (UPPER(SUBSTRING(dept, 1, 1)) + LOWER(SUBSTRING(dept, 2, LEN(dept) - 1)))
     ) tt(ORIGINAL_VALUE);