How to use a function call in a case statement without calling the function multiple times

Question

I have a db view in which one of the columns is based on the result of a scalar function (I know, bad idea). The function return will convert nulls to an empty string, however, my view needs to show null when a value is not returned. The syntax documentation of the case statement doesn't appear to offer me an answer. My "wishful thinking" solution would be to give the function call an alias and then reference that alias in the case statement like so:

SELECT
   p.Name,
   CASE GetWinningTeam(p.id, 'WON') AS Winner 
       WHEN '' THEN NULL
       ELSE Winner
   END as WinningTeam
FROM
   Projects p

instead of

 SELECT
       p.Name,
       CASE GetWinningTeam(p.id, 'WON')
           WHEN '' THEN NULL
           ELSE GetWinningTeam(p.id, 'WON')
       END as WinningTeam
    FROM
       Projects p

However, this is not valid syntax. Is there any way to make only one function call per record using a case statement, or any other solution?

Answer 1

You could use NULLIF and get rid of the case statement:

 SELECT p.Name, NULLIF(GetWinningTeam(p.id, 'WON'),'') as WinningTeam
 FROM Projects p