SQL Server Check for IsNull and for Zero

divide-by-zeroisnullsqlsql-server

I have the following:

set @SomeVariable = @AnotherVariable/isnull(@VariableEqualToZero,1) - 1 

If @VariableEqualToZero is null it substitutes the 1. I need it to substitute 1 if @VariableEqualToZero = 0 as well. How do I do this?

Best Solution

If you're using SQL Server, you can probably use a NULLIF statement?
i.e. set the value to NULL if it's 0 then set it to 1 if it's NULL - should catch for both 0's and NULLs:

SET @SomeVariable = @AnotherVariable/ISNULL(NULLIF(@VariableEqualToZero,0),1) - 1