SQL Server Check for IsNull and for Zero

sql sql-server isnull divide-by-zero
Jeff picture Jeff · Feb 24, 2009 · Viewed 77.9k times · Source

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?

Answer

Orion Leung picture Orion Leung · Oct 11, 2011

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

Related questions

Is there a opposite function to ISNULL in sql server? To do Is not null?

I have this code in my select statement ISNULL(a.PolicySignedDateTime,aq.Amount) AS 'Signed Premium', But I want to see if "a.PolicySignedDateTime" is not null. Is there a easy function to do this which does not involve using …

Read More
SQL Server: ISNULL on uniqueidentifier

I am trying to compare a column col1 and a variable @myvar in a WHERE clause. Both usually contain GUIDs, but may also have NULL values. I thought I could get around the fact that NULL=NULL evaluates to FALSE …

Read More
IsNull() sql function

I am attempting increment the value of a column field named "Number" by 1, and if the value is currently Null I would like to set the value to 1 because a Null value cannot be incremented. I discovered the isNull() function …

Read More