I've started to replace SQL CASE statements with pseudo table definitions in CTE's. The results are much more in the spirit of SET theory in my opinion and make the SQL more understandable. They code takes a "Data-Driven" and is easily converted to real tables if the need arises.
Example:
SELECT
PaintlineID
,CASE
WHEN PaintLineId = 0 THEN 'Prime'
WHEN PaintLineId = 1 THEN 'Hi-Bake'
WHEN PaintLineId = 2 THEN 'Low-Bake'
END AS PaintLineName
FROM SCHEDULE S
gets replaced with
;WITH
PaintLines AS
(
SELECT * FROM
(
VALUES
(0,'Prime')
,(1,'Hi-Bake')
,(2,'Low-Bake')
,(-1, '')
) as P(PaintLineId, PaintLineName)
)
SELECT
PL.PaintLineName
, S.PaintLineId
FROM SCHEDULE S
INNER JOIN PaintLines PL
on S.PaintLineID = PL.PaintLineId
Now if I need to add a new entry, I only have to add a new line to the pseudo table and leave the basic query intact. I add a dummy entry to the VALUES statement at the bottom so I don't mess up the comma on the last entry.
While it may seem that the data is now hard-coded into the query, I would only point out that the same is true when using a CASE statement but it's more difficult to extract.
The combination of CTE and VALUES statements have created the ability to use data driven techniques and facilitate SET based queries.