Not work well

Hi Experts,

Our partner have created function of SQL that will display amount in word in SAP B1. Here is the function:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER function [dbo].[amountinwords](@n bigint )

--Returns the number as words.

returns VARCHAR(255)

as

BEGIN

DECLARE @i int, @temp char(1), @s VARCHAR(20), @result VARCHAR(255)

SELECT @s=convert(varchar(20), @n)

SELECT @i=LEN(@s)

SELECT @result=''

WHILE (@i>0)

BEGIN

SELECT @temp=(SUBSTRING(@s,@i,1))

IF ((LEN(@s)-@i) % 3)=1

IF @temp='1'

SELECT @result=CASE (SUBSTRING(@s,@i+1,1))

WHEN '0' THEN 'Ten'

WHEN '1' THEN 'Eleven'

WHEN '2' THEN 'Twelve'

WHEN '3' THEN 'Thirteen'

WHEN '4' THEN 'Fourteen'

WHEN '5' THEN 'Fifteen'

WHEN '6' THEN 'Sixteen'

WHEN '7' THEN 'Seventeen'

WHEN '8' THEN 'Eighteen'

WHEN '9' THEN 'Nineteen'

END' 'CASE

WHEN ((LEN(@s)-@i)=4) THEN 'Thousand '

WHEN ((LEN(@s)-@i)=7) THEN 'Million '

WHEN ((LEN(@s)-@i)=10) THEN 'Billion '

WHEN ((LEN(@s)-@i)=13) THEN 'Trillion '

ELSE ''

END+@result

ELSE

BEGIN

SELECT @result=CASE (SUBSTRING(@s,@i+1,1))

WHEN '0' THEN ''

WHEN '1' THEN 'One'

WHEN '2' THEN 'Two'

WHEN '3' THEN 'Three'

WHEN '4' THEN 'Four'

WHEN '5' THEN 'Five'

WHEN '6' THEN 'Six'

WHEN '7' THEN 'Seven'

WHEN '8' THEN 'Eight'

WHEN '9' THEN 'Nine'

END' ' CASE

WHEN ((LEN(@s)-@i)=4) THEN 'Thousand '

WHEN ((LEN(@s)-@i)=7) THEN 'Million '

WHEN ((LEN(@s)-@i)=10) THEN 'Billion '

WHEN ((LEN(@s)-@i)=13) THEN 'Trillion '

ELSE ''

END+@result

SELECT @result=CASE @temp

WHEN '0' THEN ''

WHEN '1' THEN 'Ten'

WHEN '2' THEN 'Twenty'

WHEN '3' THEN 'Thirty'

WHEN '4' THEN 'Fourty'

WHEN '5' THEN 'Fifty'

WHEN '6' THEN 'Sixty'

WHEN '7' THEN 'Seventy'

WHEN '8' THEN 'Eighty'

WHEN '9' THEN 'Ninety'

END' '@result

END

IF (((LEN(@s)-@i) % 3)=2) OR (((LEN(@s)-@i) % 3)=0) AND (@i=1)

BEGIN

SELECT @result=CASE @temp

WHEN '0' THEN ''

WHEN '1' THEN 'One'

WHEN '2' THEN 'Two'

WHEN '3' THEN 'Three'

WHEN '4' THEN 'Four'

WHEN '5' THEN 'Five'

WHEN '6' THEN 'Six'

WHEN '7' THEN 'Seven'

WHEN '8' THEN 'Eight'

WHEN '9' THEN 'Nine'

END ' 'CASE

WHEN (@s='0') THEN 'Zero'

WHEN (@temp<>'0')AND( ((LEN(@s)-@i) % 3)=2) THEN 'Hundred '

ELSE ''

END + CASE

WHEN ((LEN(@s)-@i)=3) THEN 'Thousand '

WHEN ((LEN(@s)-@i)=6) THEN 'Million '

WHEN ((LEN(@s)-@i)=9) THEN 'Billion '

WHEN ((LEN(@s)-@i)=12) THEN 'Trillion '

ELSE ''

END+ @result

END

SELECT @i=@i-1

END

return REPLACE(@result,' ',' ')

END

when I excute it by using this following syntax :

select [dbo].[amountinwords](1000000), the result is one million thousand. What is the cause of this error ? I expected the result is one million only. Pls advice. I appreciate your help so much. TIA

Steve