SQL- Function to Convert Amount to Words

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author : Ramesh Subramanian
-- ALTER date : 25-July-06
-- Description : To get the amount in words
-------------------------------------------------
--SELECT dbo.AmountInWords (999999999)
-- ==============r===============================

ALTER FUNCTION [dbo].[AmountInwords] (
@numAmount NUMERIC(19,8)
)
RETURNS VARCHAR(8000)
--WITH ENCRYPTION
BEGIN
DECLARE @x NUMERIC(38,8)
DECLARE @y NUMERIC(38,8)
DECLARE @incr BIGINT
DECLARE @incr2 BIGINT
DECLARE @Remainder BIGINT
DECLARE @Remainder2 BIGINT
DECLARE @Integer BIGINT
DECLARE @MoneyinWords VARCHAR(MAX)
SET @MoneyinWords = ''
SET @x =@numAmount
SET @incr = 1
SET @incr2 = 1

DECLARE @tblDeult TABLE(val BIGINT , valstr VARCHAR(MAX))
INSERT @tblDeult
SELECT 1 , 'One'
UNION
SELECT 2 , 'Two'
UNION
SELECT 3 , 'Three'
UNION
SELECT 4, 'Four'
UNION
SELECT 5 , 'Five'
UNION
SELECT 6, 'Six'
UNION
SELECT 7 , 'Seven'
UNION
SELECT 8 , 'Eight'
UNION
SELECT 9 , 'Nine'
UNION
SELECT 10 , 'Ten'
UNION
SELECT 11 , 'Eleven'
UNION
SELECT 12 , 'Twelve'
UNION
SELECT 13, 'Thirteen'
UNION
SELECT 14 , 'Fourteen'
UNION
SELECT 15 , 'Fifteen'
UNION
SELECT 16 , 'Sixteen'
UNION
SELECT 17 , 'Seventeen'
UNION
SELECT 18 , 'Eighteen'
UNION
SELECT 19 , 'Nineteen'
UNION
SELECT 20 , 'Twenty'
UNION
SELECT 30 , 'Thirty'
UNION
SELECT 40 , 'Fourty'
UNION
SELECT 50 , 'Fifty'
UNION
SELECT 60 , 'Sixty'
UNION
SELECT 70 , 'Seventy'
UNION
SELECT 80, 'Eighty'
UNION
SELECT 90 , 'Ninety'
UNION
SELECT 100 , 'Hundred'
UNION
SELECT 1000 , 'Thousand'
UNION
SELECT 100000 , 'Lakhs'
UNION
SELECT 10000000 , 'Crores'


WHILE (@incr > 0)
BEGIN
IF (@x < 21)
BEGIN

SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @x

SET @incr = 0
END
ELSE IF (@x > 20 AND @x < 100)
BEGIN
SET @Remainder = @x % 10
SET @Integer = (@x /10)

SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer *10

SET @incr = @incr +1
SET @x = @Remainder
END
ELSE IF (@x > 99 AND @x <1000 )
BEGIN
SET @Remainder = @x % 100
SET @Integer = (@x /100)

SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer

SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = 100

SET @incr = @incr +1
SET @x = @Remainder
END
ELSE IF (@x > 999 AND @x <100000 )
BEGIN
SET @Remainder = @x % 1000
SET @Integer = (@x /1000)

IF (@Integer > 19)
BEGIN
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer/10*10
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer %10
END
ELSE
BEGIN
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer
END
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = 1000

SET @incr = @incr +1
SET @x = @Remainder
END
ELSE IF (@x > 99999 AND @x <10000000 )
BEGIN
SET @Remainder = @x % 100000
SET @Integer = (@x /100000)

IF (@Integer > 20)
BEGIN
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer/10*10
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer %10
END
ELSE
BEGIN
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer
END
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = 100000

SET @incr = @incr +1
SET @x = @Remainder
END
ELSE IF (@x > 9999999)-- AND @x <1000000000 )
BEGIN
SET @Remainder = @x % 10000000
SET @Integer = (@x /10000000)
SET @y = @Integer
SET @Remainder2 = @Remainder
IF (@Integer > 19)
BEGIN
WHILE ( @incr2> 0 )
BEGIN
IF (@y < 21)
BEGIN

SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @y

SET @incr2 = 0
END
ELSE IF (@y > 20 AND @y < 100)
BEGIN
SET @Remainder2= @y % 10
SET @Integer = (@y /10)

SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer *10

SET @incr2 = @incr2 +1
SET @y = @Remainder2
END
ELSE IF (@y > 99 AND @y <1000 )
BEGIN
SET @Remainder2= @y % 100
SET @Integer = (@y /100)

SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer

SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = 100

SET @incr2 = @incr2 +1
SET @y = @Remainder2
END
ELSE IF (@y > 999 AND @y <100000 )
BEGIN
SET @Remainder2= @y % 1000
SET @Integer = (@y /1000)

IF (@Integer > 19)
BEGIN
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer/10*10
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer %10
END
ELSE
BEGIN
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer
END
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = 1000

SET @incr2 = @incr2 +1
SET @y = @Remainder2
END
ELSE IF (@y > 99999 AND @y <10000000 )
BEGIN
SET @Remainder2= @y % 100000
SET @Integer = (@y /100000)

IF (@Integer > 20)
BEGIN
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer/10*10
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer %10
END
ELSE
BEGIN
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer
END
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = 100000

SET @incr2 = @incr2 +1
SET @y = @Remainder2
END
END

END
ELSE
BEGIN
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = @Integer
END
SELECT @MoneyinWords = @MoneyinWords + SPACE(1)+valstr
FROM @tblDeult
WHERE val = 10000000

SET @incr = @incr +1
SET @x = @Remainder
END

END

SELECT @MoneyinWords = @MoneyinWords + ' Rupees Only'


RETURN @MoneyinWords
END