IsZero Scalar Function

In situations where we want to replace zero with some other value, e.g. if division involves some computation that bring zero as a result, then it will result in divide by zero error. For such scenarios we can have IsZero function to replace zero with some other numeric value.

 

Create FUNCTION [dbo].[IsZero] (

@NumVal decimal(5,0),

@NumberIsZero decimal(5,0)

)

RETURNS DECIMAL

AS

BEGIN

 

IF (@NumVal = 0)

BEGIN

SET @NumVal = @NumberIsZero

END

 

RETURN (@NumVal)

 

END

GO

 

On running below Query

select dbo.IsZero((1*0),1)

Result will be 1

Tags: , , ,

Scalar Function | SQL Server | SQL Tricks