SQL Server Check for IsNull and for Zero

Question

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?

score 42 · Answer 1 · edited Oct 23 '19 at 16:19

42

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

edited Oct 23 '19 at 16:19

wp78de

18,207
7
43
71

answered Oct 11 '11 at 06:22

Orion Leung

429
1
4
2

+1 `NULLIF()` is the way to go (and it's Standard SQL too, so I'd be tempted to replace `ISNULL()` with `COALESCE()` :) – onedaywhen Oct 11 '11 at 07:31

score 17 · Accepted Answer · edited May 12 '14 at 10:38

17

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

edited May 12 '14 at 10:38

Yaroslav

6,476
10
48
89

answered Feb 24 '09 at 03:37

user53794

3,800
2
30
31

instead of coalasce() and Case() just use a CASE with isnull? ISNULL([@VariableEqualToZero],0) = 0 THEN 1 – AquaAlex Jul 03 '19 at 10:23

score 4 · Answer 3 · answered Feb 24 '09 at 03:37

4

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

answered Feb 24 '09 at 03:37

Eric Rosenberger

8,987
1
23
24

score 3 · Answer 4 · answered Feb 24 '09 at 03:43

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.

SQL Server Check for IsNull and for Zero

4 Answers4

Linked