Amount Into Word Script in T-SQL

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.
By Anil Desai   Popularity  (1001 Views)