Use T-SQL's NULLIF To Fix Divide By Zero Errors

Lately I have doing a lot of SSRS and overall SQL work for a project.  In returning some data to process server-side my data retrieval method was returning null* which means that an exception was thrown. I did a little digging around and then decided to execute the stored procedure manually (i.e. Management Studio).  After debugging in Visual Studio and seeing what the values were and that they were in fact getting passed to the stored procedure I used the same signature to execute the proc in Management Studio.  Hmm, no dice.   My error message was of a "Divide-By-Zero" fashion.  This is the first time that I have ever seen this.  A little reading on the subject and I come to find out that the field used as the denominator allowed nulls.

Enter NULLIF. This is what saved the day.  This function will compare two values and return a null value if the expressions are equal.  For example, take the following dummy stored procedure.

CREATE Procedure SharesCalculateSharePrice
(
   
@shareId int,
   
@numberOfUnits decimal
)

AS

DECLARE @totalPrice decimal

SET @totalPrice = ( SELECT TotalPrice FROM Shares WHERE ShareId = @shareId )
DECLARE @returnValue decimal

SET @returnValue = ( @numberOfUnits / @totalPrice )
SELECT @returnValue 

Pretty basic. I'm selecting the total price from a table and using as the denominator.  I pass in number of units. To get price per unit (share price) I divide one by the other. What if the @totalPrice is 0? Then I get the dreaded Divide-By-Zero error.  Using NULLIF, we can make a trivial aleration to the above procedure and return a NULL value instead.

CREATE Procedure SharesCalculateSharePrice
(
   
@shareId int,
   
@numberOfUnits decimal
)

AS

DECLARE @totalPrice decimal

SET @totalPrice = ( SELECT SUM(TotalPrice) FROM Shares WHERE ShareId = @shareId )
DECLARE @returnValue decimal

SET @returnValue = ( @numberOfUnits / NULLIF(@totalPrice, 0) )
SELECT @returnValue 

Easy. If @totalPrice is 0 we will substitute with a null value.  This way the returnValue will be NULL and we can process accordingly from our .NET application.

public static object ExecuteScalar(List<IDbDataParameter> parameters, string commandText)
{
   using (DBManager manager = new DBManager(_provider, _connectionString))
   {
      manager.Open();
      manager.CreateParameters(parameters.Count);

      for (int i = 0; i < parameters.Count; ++i)
         manager.AddParameters(i, parameters[i].ParameterName, parameters[i].Value);

      object returnValue = manager.ExecuteScalar(CommandType.StoredProcedure, commandText);

      return returnValue == DBNull.Value || returnValue == null ? -1 : returnValue;
   }

There could be even more useful ways to use NULLIF but for me this has worked out fine.

Comments

Popular posts from this blog

Yet once more into the breech (of altered programming logic)

Simple WP7 Mango App for Background Tasks, Toast, and Tiles: Code Explanation

How to convert SVG data to a Png Image file Using InkScape