Return 1 if value is 0 without CASE statement

Question

I'm trying to find a tidier way of doing the below query so that I'm not duplicating my code.

SELECT CASE WHEN <COMPLICATED CODE THAT RETURNS A SINGLE INT> = 0
THEN 1
ELSE <COMPLICATED CODE THAT RETURNS A SINGLE INT> END

Ideally, I would like something like this using an existing function rather than creating my own:

SELECT ISVALUE(COMPLICATED CODE THAT RETURNS A SINGLE INT,0,1)

Answer 1

You can use apply:

SELECT (CASE WHEN v.val = 0 THEN 1 ELSE v.val END)
FROM . . . CROSS APPLY
     (VALUES (<COMPLICATED CODE THAT RETURNS A SINGLE INT>)) v(val);

You could also do a series of functions:

select coalesce(nullif(<COMPLICATED CODE THAT RETURNS A SINGLE INT>, 0), 1)

However, I think apply is clearer. In addition the above will turn NULL values into 1 as well as 0.