SQL Server Check for IsNull and for Zero

问题:

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?

回答1:

SET @SomeVariable = @AnotherVariable / COALESCE(
        CASE 
             WHEN @VariableEqualToZero = 0 THEN 1
             ELSE @VariableEqualToZero
        END, 1) - 1

回答2:

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

回答3:

set @SomeVariable = @AnotherVariable /
(case when isnull(@VariableEqualToZero, 0) = 0 then 1 else
@VariableEqualToZero end) - 1

回答4:

You use CASE

instead of

ISNULL(@VariableEqualToZero,1)

use

CASE WHEN @VariableEqualToZero IS NULL OR @VariableEqualToZero = 0 THEN 1 ELSE @VariableEqualToZero END

COALESCE and ISNULL are essentially just shortcuts for a CASE statement. You can consult the help for the syntax of CASE.