Function SQL gives wrong result

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Function SQL gives wrong result

Author

Topic

anthoni76
Starting Member

3 Posts

Posted - 2010-06-23 : 22:26:33

Hi Experts,

Our partner have created SQL function amount in word. 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

Antho

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-23 : 22:49:49

you can refer to
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11157

KH
[spoiler]Time is always against us[/spoiler]

anthoni76
Starting Member

3 Posts

Posted - 2010-06-23 : 23:05:08

Thx for the link but I could not use it because the currency used here is indonesian. The amount in word sometimes is 1 billion. In the link, I did not see the word "billion".

I also would not like to replace the current function I used.
Please give guidance or solution to improve the function I pasted in this message. It should not give one million thousand, but one million.

if you have time to use the function and run it, you will find that the result of the function is not correct.
I need your help to improve the function so that the result is correct. Pls help.

Antho

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-23 : 23:10:34

you can modify and expand that query accordingly to your requirement

KH
[spoiler]Time is always against us[/spoiler]

anthoni76
Starting Member

3 Posts

Posted - 2010-06-23 : 23:25:16

Hi KH,

Thx for help.
It is impossible for me to use it because the function I use now is also used in the coding. Actually, this function is executed in the sap business one (SBO) database --> programmability. using SDK code (coding developed using VB.net and using SDK code as reference ad the funtion), the amount in word will be automatically entered into the certain table field of the SBO application. For example, the invoice table. The invoice document has a field named amount in word. When I activated the addon, the amount in word is automatically entered by the addon.
So, I am not sure I can't use the coding in the link you have made.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources