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
Post a Comment