SQL Server (T-SQL)

As a Dynamics developer, I'm frequently asked to divide by the USCATVLS fields in the IV00101 table. Since they're CHAR types, there is often no validation on empty values or numeric values. The funtion below will take two VARCHAR values and divide them, returning '0' if there is some validation issue. The code at the end is a test script.

 IF OBJECT_ID (N'dbo.f_FP_divide') IS NOT NULL
   DROP FUNCTION dbo.f_FP_divide
GO


CREATE FUNCTION f_FP_divide 
/*
select 
    dbo.f_FP_divide('0','0'),
    dbo.f_FP_divide('a','0'),
    dbo.f_FP_divide('0','a'),
    dbo.f_FP_divide('2','0'),
    dbo.f_FP_divide('0','2'),
    dbo.f_FP_divide('15','2')



*/


(
    @vchrNum1 as varchar(20),
    @vchrNum2 as varchar(20)
)
RETURNS numeric(19,5)
AS
BEGIN
    declare @nNum1 as numeric(19,5),
        @nNum2 as numeric(19,5)


    if isnumeric(@vchrNum1) = 0 begin
        return 0
    end
    if isnumeric(@vchrNum2) = 0 begin
        return 0
    end


    select @nNum1 = @vchrNum1,
        @nNum2 = @vchrNum2


    if @nNum2 = 0 begin
        return 0
    end



    return @nNum1 / @nNum2
END
GO

To test:

select amtwork.sopnumbe, inv.itemnmbr,
        amtwork.qtyfulfi,
        inv.uscatvls_4,
        dbo.f_FP_divide(AMTWORK.QTYFULFI,INV.USCATVLS_4) as CARTONS
    from sop30300 amtwork
        join iv00101 inv on inv.itemnmbr = amtwork.itemnmbr