IsZero

When you divide the numeric by zero,you'll get the error message.

Message errorServer: Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

You can use this function to solve this problem.

Create functon :

CREATE FUNCTION dbo.iszero(@Value numeric,@Replace int)
RETURNS int
AS
BEGIN
DECLARE @Result int
IF @Value = 0
SET @Result = @Replace
ELSE
SET @Result = @Value
RETURN @Result
END

Test : shows null if function returns null value.

SELECT
Id AS ID,
[IND Volume],
[Volume Grade],
[Volume Grade] / dbo.iszero([IND Volume],null) AS DIV_Result
FROM dbo.LampCustomerAll
WHERE (ASM_Code = 'LM5000') AND ([IND Volume] = 0) OR ([IND Volume] = 2)
ORDER BY Id DESC

Result :

How to create function

How to create function

CREATE FUNCTION owner_name.function_name ( @parameter_name AS data_type ,...n )
RETURNS data_type
AS
BEGIN
function_body
RETURN value
END


Examples :

CREATE FUNCTION dbo.GetNoWeek (@var Varchar(10))
RETURNS varchar(10)
BEGIN
DECLARE @result varchar(10)
IF @var = 'Ok'
SET @Result = 'Ok'
ELSE
SET @Result = 'Not Ok'
RETURN @Result
END

AddZeroDigit

How to add Zero to an integer value from left (like 9 to 009)

Create function :

CREATE FUNCTION dbo.AddZeroDigit(@Value AS int)
RETURNS varchar(3)

BEGIN

DECLARE @Result varchar(3)

SET @Result = RIGHT('000'+CAST(@Value AS varchar(3)),3)

RETURN @ResultEND

Test :

SELECT dbo.AddZeroDigit(9) AS Result

Result :

DayNameOfWeek

How to get the day name of week .

Create function :

CREATE FUNCTION dbo.DayOfWeek(@Date datetime)
RETURNS varchar(10)
BEGINDECLARE @Today intDECLARE @Result varchar(10)
SET @Today = DATEPART(dw, @Date)

IF @Today = 1 SET @Result = 'SUN'
ELSE IF @Today = 2 SET @Result = 'MON'
ELSE IF @Today = 3 SET @Result = 'TUE'
ELSE IF @Today = 4 SET @Result = 'WED'
ELSE IF @Today = 5 SET @Result = 'THU'
ELSE IF @Today = 6 SET @Result = 'FRI'
ELSE IF @Today = 7 SET @Result = 'SAT'
RETURN @ResultEND

Test :

SET DATEFIRST 7
select dbo.DayOfWeek('2007-09-05') as ToDay

Result :