Sehajpal.com

I know it is not that much crucial but neither easy to get rid of financial application’s requirements as clients demand more and more facilities even they are small in size.

Here is one that kinda SQL Script that every finance domain expert looks for. That is the currency writer in WORDS instead of digits.

CREATE FUNCTION dbo.AMOUNT_IN_WORDS
(
 @Amount INT,
 @PAISE INT
)
RETURNS int

BEGIN

DECLARE
–Step 1:
–Here we define the temporary tables to hold the WORDs representation of possible digits/amounts

DECLARE @INNTBL_01 TABLE (RECNO INT IDENTITY(1, 1), MTEXT NVARCHAR(50))

DECLARE @INNTBL_02 TABLE (RECNO INT IDENTITY(1, 1), MTEXT NVARCHAR(50))

–Step 2:
–Now add up the salt and pepper onto table  i.e. insert the WORDs into it

INSERT INTO @INNTBL_01 SELECT ‘ONE’
INSERT INTO @INNTBL_01 SELECT ‘TWO’
INSERT INTO @INNTBL_01 SELECT ‘THREE’
INSERT INTO @INNTBL_01 SELECT ‘FOUR’
INSERT INTO @INNTBL_01 SELECT ‘FIVE’
INSERT INTO @INNTBL_01 SELECT ‘SIX’
INSERT INTO @INNTBL_01 SELECT ‘SEVEN’
INSERT INTO @INNTBL_01 SELECT ‘EIGHT’
INSERT INTO @INNTBL_01 SELECT ‘NINE’
INSERT INTO @INNTBL_01 SELECT ‘TEN’
INSERT INTO @INNTBL_01 SELECT ‘ELEVEN’
INSERT INTO @INNTBL_01 SELECT ‘TWELVE’
INSERT INTO @INNTBL_01 SELECT ‘THIRTEEN’
INSERT INTO @INNTBL_01 SELECT ‘FOURTEEN’
INSERT INTO @INNTBL_01 SELECT ‘FIFTEEN’
INSERT INTO @INNTBL_01 SELECT ‘SIXTEEN’
INSERT INTO @INNTBL_01 SELECT ‘SEVENTEEN’
INSERT INTO @INNTBL_01 SELECT ‘EIGHTEEN’
INSERT INTO @INNTBL_01 SELECT ‘NINETEEN’
INSERT INTO @INNTBL_01 SELECT ‘TWENTY’–Similary, insert the multiples

INSERT INTO @INNTBL_02 SELECT ‘TEN’
INSERT INTO @INNTBL_02 SELECT ‘TWENTY’
INSERT INTO @INNTBL_02 SELECT ‘THIRTY’
INSERT INTO @INNTBL_02 SELECT ‘FORTY’
INSERT INTO @INNTBL_02 SELECT ‘FIFTY’
INSERT INTO @INNTBL_02 SELECT ‘SIXTY’
INSERT INTO @INNTBL_02 SELECT ‘SEVENTY’
INSERT INTO @INNTBL_02 SELECT ‘EIGHTY’
INSERT INTO @INNTBL_02 SELECT ‘NINETY’

–Step 3:
–Check for the limit of the amount i.e. what is the place value of digits – LACs, Thousands or Hundreds
DECLARE @WORD VARCHAR(300)
SELECT @WORD = ”

DECLARE @M_AMT01  INT, @M_AMT02 INT

IF @AMOUNT < 10000000 AND @AMOUNT >= 100000 BEGIN
SET @M_AMT01 = @AMOUNT
SELECT @AMOUNT = ( @AMOUNT % 100000 )
SET @M_AMT01 = ( @M_AMT01 – @AMOUNT ) / 100000
DECLARE @WORD1 VARCHAR(300)
SET @WORD1 = ”
IF @M_AMT01 < 100 AND @M_AMT01 > 20 BEGIN
SET @M_AMT02 = @M_AMT01
SET @M_AMT01 = ( @M_AMT01 % 10)
SET @M_AMT02 = ( @M_AMT02 – @M_AMT01 ) / 10
SET @WORD1 = ( SELECT @WORD1 + MTEXT FROM @INNTBL_02 WHERE RECNO = @M_AMT02 )
END

IF @M_AMT01 <= 20 AND @M_AMT01 <> 0 BEGIN
SET @WORD1 = ( SELECT @WORD1 + MTEXT FROM @INNTBL_01 WHERE RECNO = @M_AMT01 )
END
SET @WORD = @WORD + @WORD1 + ‘ LAC ‘
END
IF @AMOUNT < 100000 AND @AMOUNT >= 1000 BEGIN
SET @M_AMT01 = @AMOUNT
SET @AMOUNT = ( @AMOUNT % 1000 )
SET @M_AMT01 = ( @M_AMT01 – @AMOUNT ) / 1000
SET @WORD1 = ”
IF @M_AMT01 < 100 AND @M_AMT01 > 20 BEGIN
SET @M_AMT02 = @M_AMT01
SET @M_AMT01 = ( @M_AMT01 % 10 )
SET @M_AMT02 = ( @M_AMT02 – @M_AMT01 ) / 10
SET @WORD1 = ( SELECT @WORD1 + MTEXT + ‘ ‘ FROM @INNTBL_02 WHERE RECNO = @M_AMT02 )
END
IF @M_AMT01 <= 20 AND @M_AMT01 <> 0 BEGIN
SET @WORD1 = ( SELECT @WORD1 + MTEXT +’ ‘ FROM @INNTBL_01 WHERE RECNO = @M_AMT01 )
END
SET @WORD = @WORD + @WORD1 + ‘ THOUSAND ‘
END
IF @AMOUNT < 1000 AND @AMOUNT > = 100 BEGIN
SET @M_AMT01 = @AMOUNT
SET @AMOUNT = ( @AMOUNT % 100 )
SET @M_AMT01 = ( @M_AMT01 – @AMOUNT ) / 100
SET @WORD = ( SELECT @WORD + ‘ ‘ +MTEXT + ‘ HUNDRED ‘ FROM @INNTBL_01 WHERE RECNO = @M_AMT01

)
END
IF @AMOUNT < 100 AND @AMOUNT > 20 BEGIN
SET @M_AMT01 = @AMOUNT
SET @AMOUNT = ( @AMOUNT % 10 )
SET @M_AMT01 = ( @M_AMT01 – @AMOUNT ) / 10
SET @WORD = ( SELECT @WORD + MTEXT + ‘ ‘ FROM @INNTBL_02 WHERE RECNO = @M_AMT01 )
END
IF @AMOUNT <= 20 AND @AMOUNT >= 1 BEGIN
SET @WORD = ( SELECT @WORD + MTEXT +’ ‘ FROM @INNTBL_01 WHERE RECNO = @AMOUNT )
END

–STEP 4:
–Calculate the paise also.
DECLARE @WORDP VARCHAR(300)
SET @WORDP = ”
IF @PAISE <> 0 BEGIN
IF @PAISE < 100 AND @PAISE > 20 BEGIN
DECLARE @PAISE_01 VARCHAR(300)
SET @PAISE_01 = @PAISE
SET @PAISE = ( @PAISE % 10 )
SET @PAISE_01 = ( @PAISE_01 – @PAISE ) / 10
SET @WORDP = ( SELECT @WORDP + MTEXT FROM @INNTBL_02 WHERE RECNO = @PAISE_01 )
END
IF @PAISE <= 20 AND @PAISE >= 1 BEGIN
SET @WORDP = ( SELECT @WORDP + MTEXT FROM @INNTBL_01 WHERE RECNO = @PAISE )
END
SET @WORD = @WORD + ‘AND ‘ + @WORDP + ‘ PAISE’
END

–STEP 5:
–Time to return answer from the function.
RETURN (REPLACE(@WORD, ‘  ‘, ‘ ‘))

–Its done dude. Try by yourself.
END

Now when you need to use this function, simply call it inline to any select statement.

CALLING THE FUNCTION

Select dbo.AMOUNT_IN_WORDS(IM.GRAND_TOTAL, 0) AS words from TBL_INVOICE_MAIN IM

Here, it will convert GRAND_TOTAL field from the table TBL_INVOICE_MAIN.

or more simply,

Select dbo.AMOUNT_IN_WORDS(654321, 0)

Isn’t it simple enough !