SQL Server (T-SQL)

How to avoid the dreaded 'divide by zero' error

2008-10-27T14:34:00Z

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

Function to 'Proper' case a string

2008-10-23T21:53:00Z

Occasionally I'll need to format a string in SQL with 'Proper' casing. In other words, change 'steve gray' to 'Steve Gray'.

Here is a function to do that

 CREATE  FUNCTION [dbo].[f_FP_Capitalize] 
-- select dbo.f_FP_Capitalize ('steve gray')
( 
@string VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS 
BEGIN


DECLARE @Next INT
WHILE 1=1
       BEGIN
       --find word space followed by lower case letter
       --This makes assumptions about the language
       SELECT @next= 
           PATINDEX('%[^a-zA-Z][abcdefghijklmnopqurstuvwzyz]%',
                       ' <a href="mailto:'+@string">'+@string</a>  collate Latin1_General_CS_AI)
       IF @next =0 BREAK
       SELECT @String = 
           STUFF(@String,@Next,1,UPPER(SUBSTRING(@String,@Next,1)))
       END
RETURN @string
END

How to tell if a temp table exists in SQL Server

2008-10-23T21:21:00Z

Tested in SQL 2000 & 2005:

 IF OBJECT_ID('tempdb..#tmp') IS NOT NULL 
    PRINT 'exists.' 
ELSE 
    PRINT 'does not exist.'