Amount Into Word Script in T-SQL By Anil Desai Printer Friendly Version

Here this script display amount into word.

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[CalRupees] ( @rupee int ) RETURNS varchar(100) AS BEGIN -- Declare the return variable here DECLARE @result varchar(100) -- Add the T-SQL statements to compute the return value here --SET @result = 'Anil' if(@rupee >= 1 AND @rupee <= 10) begin if((@rupee % 10) = 1) set @result = 'One' if((@rupee % 10) = 2) set @result = 'Two' if((@rupee % 10) = 3) set @result = 'Three' if((@rupee % 10) = 4) set @result = 'Four' if((@rupee % 10) = 5) set @result = 'Five' if((@rupee % 10) = 6) set @result = 'Six' if((@rupee % 10) = 7) set @result = 'Seven' if((@rupee % 10) = 8) set @result = 'Eight' if((@rupee % 10) = 9) set @result = 'Nine' if((@rupee % 10) = 0) set @result = 'Ten' --set @result = cast(@rupee as varchar) END if(@rupee > 9 AND @rupee < 20) begin if(@rupee = 11) set @result = 'Eleven' if(@rupee = 12) set @result = 'Twelve' if(@rupee = 13) set @result = 'Thirteen' if(@rupee = 14) set @result = 'Forteen' if(@rupee = 15) set @result = 'Fifteen' if(@rupee = 16) set @result = 'Sixteen' if(@rupee = 17) set @result = 'Seventeen' if(@rupee = 18) set @result = 'Eighteen' if(@rupee = 19) set @result = 'Nineteen' end if(@rupee > 20 AND (@rupee / 10) = 2 AND (@rupee % 10) = 0) set @result = 'Twenty' if(@rupee > 20 AND (@rupee / 10) = 3 AND (@rupee % 10) = 0) set @result = 'Thirty' if(@rupee > 20 AND (@rupee / 10) = 4 AND (@rupee % 10) = 0) set @result = 'Forty' if(@rupee > 20 AND (@rupee / 10) = 5 AND (@rupee % 10) = 0) set @result = 'Fifty' if(@rupee > 20 AND (@rupee / 10) = 6 AND (@rupee % 10) = 0) set @result = 'Sixty' if(@rupee > 20 AND (@rupee / 10) = 7 AND (@rupee % 10) = 0) set @result = 'Seventy' if(@rupee > 20 AND (@rupee / 10) = 8 AND (@rupee % 10) = 0) set @result = 'Eighty' if(@rupee > 20 AND (@rupee / 10) = 9 AND (@rupee % 10) = 0) set @result = 'Ninty' if(@rupee > 20 AND (@rupee / 10) = 2 AND (@rupee % 10) != 0) begin if((@rupee % 10) = 1) set @result = 'Twenty One' if((@rupee % 10) = 2) set @result = 'Twenty Two' if((@rupee % 10) = 3) set @result = 'Twenty Three' if((@rupee % 10) = 4) set @result = 'Twenty Four' if((@rupee % 10) = 5) set @result = 'Twenty Five' if((@rupee % 10) = 6) set @result = 'Twenty Six' if((@rupee % 10) = 7) set @result = 'Twenty Seven' if((@rupee % 10) = 8) set @result = 'Twenty Eight' if((@rupee % 10) = 9) set @result = 'Twenty Nine' end if(@rupee > 20 AND (@rupee / 10) = 3 AND (@rupee % 10) != 0) begin if((@rupee % 10) = 1) set @result = 'Thirty One' if((@rupee % 10) = 2) set @result = 'Thirty Two' if((@rupee % 10) = 3) set @result = 'Thirty Three' if((@rupee % 10) = 4) set @result = 'Thirty Four' if((@rupee % 10) = 5) set @result = 'Thirty Five' if((@rupee % 10) = 6) set @result = 'Thirty Six' if((@rupee % 10) = 7) set @result = 'Thirty Seven' if((@rupee % 10) = 8) set @result = 'Thirty Eight' if((@rupee % 10) = 9) set @result = 'Thirty Nine' end if(@rupee > 20 AND (@rupee / 10) = 4 AND (@rupee % 10) != 0) begin if((@rupee % 10) = 1) set @result = 'Forty One' if((@rupee % 10) = 2) set @result = 'Forty Two' if((@rupee % 10) = 3) set @result = 'Forty Three' if((@rupee % 10) = 4) set @result = 'Forty Four' if((@rupee % 10) = 5) set @result = 'Forty Five' if((@rupee % 10) = 6) set @result = 'Forty Six' if((@rupee % 10) = 7) set @result = 'Forty Seven' if((@rupee % 10) = 8) set @result = 'Forty Eight' if((@rupee % 10) = 9) set @result = 'Forty Nine' end if(@rupee > 20 AND (@rupee / 10) = 5 AND (@rupee % 10) != 0) begin if((@rupee % 10) = 1) set @result = 'Fifty One' if((@rupee % 10) = 2) set @result = 'Fifty Two' if((@rupee % 10) = 3) set @result = 'Fifty Three' if((@rupee % 10) = 4) set @result = 'Fifty Four' if((@rupee % 10) = 5) set @result = 'Fifty Five' if((@rupee % 10) = 6) set @result = 'Fifty Six' if((@rupee % 10) = 7) set @result = 'Fifty Seven' if((@rupee % 10) = 8) set @result = 'Fifty Eight' if((@rupee % 10) = 9) set @result = 'Fifty Nine' end if(@rupee > 20 AND (@rupee / 10) = 6 AND (@rupee % 10) != 0) begin if((@rupee % 10) = 1) set @result = 'Sixty One' if((@rupee % 10) = 2) set @result = 'Sixty Two' if((@rupee % 10) = 3) set @result = 'Sixty Three' if((@rupee % 10) = 4) set @result = 'Sixty Four' if((@rupee % 10) = 5) set @result = 'Sixty Five' if((@rupee % 10) = 6) set @result = 'Sixty Six' if((@rupee % 10) = 7) set @result = 'Sixty Seven' if((@rupee % 10) = 8) set @result = 'Sixty Eight' if((@rupee % 10) = 9) set @result = 'Sixty Nine' end if(@rupee > 20 AND (@rupee / 10) = 7 AND (@rupee % 10) != 0) begin if((@rupee % 10) = 1) set @result = 'Seventy One' if((@rupee % 10) = 2) set @result = 'Seventy Two' if((@rupee % 10) = 3) set @result = 'Seventy Three' if((@rupee % 10) = 4) set @result = 'Seventy Four' if((@rupee % 10) = 5) set @result = 'Seventy Five' if((@rupee % 10) = 6) set @result = 'Seventy Six' if((@rupee % 10) = 7) set @result = 'Seventy Seven' if((@rupee % 10) = 8) set @result = 'Seventy Eight' if((@rupee % 10) = 9) set @result = 'Seventy Nine' end if(@rupee > 20 AND (@rupee / 10) = 8 AND (@rupee % 10) != 0) begin if((@rupee % 10) = 1) set @result = 'Eighty One' if((@rupee % 10) = 2) set @result = 'Eighty Two' if((@rupee % 10) = 3) set @result = 'Eighty Three' if((@rupee % 10) = 4) set @result = 'Eighty Four' if((@rupee % 10) = 5) set @result = 'Eighty Five' if((@rupee % 10) = 6) set @result = 'Eighty Six' if((@rupee % 10) = 7) set @result = 'Eighty Seven' if((@rupee % 10) = 8) set @result = 'Eighty Eight' if((@rupee % 10) = 9) set @result = 'Eighty Nine' end if(@rupee > 20 AND (@rupee / 10) = 9 AND (@rupee % 10) != 0) begin if((@rupee % 10) = 1) set @result = 'Ninty One' if((@rupee % 10) = 2) set @result = 'Ninty Two' if((@rupee % 10) = 3) set @result = 'Ninty Three' if((@rupee % 10) = 4) set @result = 'Ninty Four' if((@rupee % 10) = 5) set @result = 'Ninty Five' if((@rupee % 10) = 6) set @result = 'Ninty Six' if((@rupee % 10) = 7) set @result = 'Ninty Seven' if((@rupee % 10) = 8) set @result = 'Ninty Eight' if((@rupee % 10) = 9) set @result = 'Ninty Nine' end RETURN @result END GO ------------------------------------------------------------------------------------------------- declare @acceptedAmount bigint declare @rem bigint declare @result varchar(500) set @result ='' set @acceptedAmount = 111111111 print ' Amount : '+ cast(@acceptedAmount as varchar) if((@acceptedAmount / 10000000) > 0) --print 'Caror' begin set @rem = @acceptedAmount / 10000000 set @acceptedAmount = @acceptedAmount % 10000000 set @result = @result + ' ' + [dbo].[CalRupees](@rem) + ' Caror' end if((@acceptedAmount / 100000) > 0) --print 'Lack' begin set @rem = @acceptedAmount / 100000 set @acceptedAmount = @acceptedAmount % 100000 set @result = @result + ' ' + [dbo].[CalRupees](@rem) + ' Lack' end if((@acceptedAmount / 1000) > 0) --print 'Thousand' begin set @rem = @acceptedAmount / 1000 set @acceptedAmount = @acceptedAmount % 1000 set @result = @result + ' ' + [dbo].[CalRupees](@rem) + ' Thousand' end if((@acceptedAmount / 100) > 0) --print 'Hundred' begin set @rem = @acceptedAmount / 100 set @acceptedAmount = @acceptedAmount % 100 set @result = @result + ' ' + [dbo].[CalRupees](@rem) + ' Hundred' end if((@acceptedAmount % 10) > 0) --print 'Ten' begin set @rem = @acceptedAmount % 100 --print @amount set @result = @result + ' ' + [dbo].[CalRupees](@rem) end set @result = @result + ' ' + ' Rs.' print ' Amount in word :' print @result ------------------------------------------------------------------------------------------------- drop function [dbo].[CalRupees] ------------------------------------------------------------------------------------------------- Output will be... Amount : 111111111 Amount in word : Eleven Caror Eleven Lack Eleven Thousand One Hundred Eleven Rs.

ELIMINATE DIVIDE BY ZERO

SQL Server Reporting Adding Row Totals To Matrix Table Object

Unable to Restore Data File in Small Bus Acct. 2006 SP3 MOSBA

Treeview Navigation - SqlCacheDependency and SqlSitemapProvider

HOSTING THE CLR IN SQL SERVER 2005

Article Discussion: Amount Into Word Script in T-SQL

Anil Desai posted at 09-Apr-08 03:33

Original Article

There is definitely better ways

noneof yourbusiness replied to Anil Desai at 09-Apr-08 10:14

There are tons of better ways to accomplish this task.  This is the most long winded way of coming to a solution.  I can only hope that others use this as an example of how NOT to do it.