Improving XML User Defined Function Performance

Doing some code review, I came across a piece of code which giving

 

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.

 

The likely cause was someone entering a ‘n/a’ in the field (the curse of XML coming from slack systems).

Create FUNCTION [dbo].[GetSellingPrice]

(

    @Data xml

)

RETURNS decimal(20,2)

WITH SCHEMABINDING

AS

BEGIN

    declare @Return decimal(20,2)

    select @Return =

            CASE

            WHEN @Data.value('(//Property/@SellingPrice)[1]', 'nvarchar(20)') IS NULL THEN 0

            ELSE @Data.value('(//Property/@SellingPrice)[1]', 'decimal(20,2)')

        END

    RETURN @Return

END

Even with good data, this code is not ideal for several reasons:

• Multiple calls to the .value function to get the same data
• Using nvarchar when varchar is sufficient (after all, it’s being cast to a number!)
• Searching the entire XML every call

My proposed alternative TSQL is below which addresses these issues.

CREATE FUNCTION [dbo].[GetSellingPrice2]

(

    @Data xml

)

RETURNS decimal(20,2)

WITH SCHEMABINDING

AS

BEGIN

    declare @Return decimal(20,2)

    declare @text varchar(20)

    Set @text=@Data.value('(Property/@SellingPrice)[1]', 'varchar(20)') 

    if @Text is null OR Len(@Text) < 4 

        SET @Return=0

    ELSE        

        Set @Return=Cast(@text as Decimal(20,2)    )

    RETURN @Return

END

One of the problem exposed is an interesting one – there is no IsNaN() function in TSQL  which would make this kludge unneeded.  So my additional suggestion would be to create a CLR function that provides IsNaN() functionality.