Just plug this into your Query Analyser and play. This code is an easier way of converting currency to English which can be altered easily to suit your needs.
I found that this script will help in avoiding the storage of Amount in Words everytime an amount is stored in applicatons that require the functionality.
CREATE FUNCTION dbo.ConvertDigit
(
@MyDigit VARCHAR(300)
)
RETURNS VARCHAR(300)
AS
BEGIN
DECLARE @DigitInWords VARCHAR(300)
SET @DigitInWords=
CASE
WHEN @MyDigit='1' THEN 'One'
WHEN @MyDigit='2' THEN 'Two'
WHEN @MyDigit='3' THEN 'Three'
WHEN @MyDigit='4' THEN 'Four'
WHEN @MyDigit='5' THEN 'Five'
WHEN @MyDigit='6' THEN 'Six'
WHEN @MyDigit='7' THEN 'Seven'
WHEN @MyDigit='8' THEN 'Eight'
WHEN @MyDigit='9' THEN 'Nine'
ELSE ''
END
RETURN @DigitInWords
END
GO
CREATE FUNCTION dbo.ConvertTens
(
@MyTens VARCHAR(300)
)
RETURNS VARCHAR(300)
AS
BEGIN
DECLARE @Result VARCHAR(300)
-- Is value between 10 and 19?
IF (LEFT(@MyTens, 1)) = '1'
BEGIN
SET @Result=
CASE
WHEN @MyTens ='10' THEN 'Ten'
WHEN @MyTens ='11' THEN 'Eleven'
WHEN @MyTens ='12' THEN 'Twelve'
WHEN @MyTens ='13' THEN 'Thirteen'
WHEN @MyTens ='14' THEN 'Fourteen'
WHEN @MyTens ='15' THEN 'Fifteen'
WHEN @MyTens ='16' THEN 'Sixteen'
WHEN @MyTens ='17' THEN 'Seventeen'
WHEN @MyTens ='18' THEN 'Eighteen'
WHEN @MyTens ='19' THEN 'Nineteen'
ELSE ''
END
END
ELSE
BEGIN
-- .. otherwise it's between 20 and 99.
SET @Result=
CASE
WHEN (LEFT(@MyTens, 1))='2' THEN 'Twenty '
WHEN (LEFT(@MyTens, 1))='3' THEN 'Thirty '
WHEN (LEFT(@MyTens, 1))='4' THEN 'Forty '
WHEN (LEFT(@MyTens, 1))='5' THEN 'Fifty '
WHEN (LEFT(@MyTens, 1))='6' THEN 'Sixty '
WHEN (LEFT(@MyTens, 1))='7' THEN 'Seventy '
WHEN (LEFT(@MyTens, 1))='8' THEN 'Eighty '
WHEN (LEFT(@MyTens, 1))='9' THEN 'Ninety '
ELSE ''
END
-- Convert ones place digit.
SET @Result =ISNULL(@Result,'') + dbo.ConvertDigit(RIGHT(@MyTens, 1))
END
RETURN @Result
END
GO
CREATE FUNCTION dbo.ConvertHundreds
(
@MyNumber VARCHAR(300)
)
RETURNS VARCHAR(300)
AS
BEGIN
DECLARE @Result VARCHAR(300)
-- Exit if there is nothing to convert.
IF ISNULL(@MyNumber,'')=''
BEGIN
RETURN ''
END
-- Append leading zeros to number.
SET @MyNumber = RIGHT('000' + @MyNumber, 3)
-- Do we have a hundreds place digit to convert?
IF LEFT(@MyNumber, 1) <> '0'
BEGIN
SET @Result = dbo.ConvertDigit(LEFT(@MyNumber, 1)) + ' Hundred '
END
-- Do we have a tens place digit to convert?
IF SUBSTRING(@MyNumber, 2, 1) <> '0'
BEGIN
SET @Result = ISNULL(@Result,'') + dbo.ConvertTens( SUBSTRING( @MyNumber, 2,LEN(@MyNumber) ) )
END
ELSE
BEGIN
-- If not, convert the ones place digit.
SET @Result = ISNULL(@Result,'') + dbo.ConvertDigit(SUBSTRING(@MyNumber, 3,LEN(@MyNumber)))
END
RETURN @Result
END
GO
CREATE FUNCTION ConvertCurrencyToEnglish
(
@MyNumber VARCHAR(200)
)
RETURNS VARCHAR(300)
AS
BEGIN
DECLARE @Temp VARCHAR(300)
DECLARE @Dollars VARCHAR(300), @Cents VARCHAR(300)
DECLARE @DecimalPlace INT, @Count INT
DECLARE @MultiplicationFactor VARCHAR(50)
-- Convert MyNumber to a string, trimming extra spaces.
SET @MyNumber = CAST(@MyNumber AS DECIMAL(18,2))
-- Find decimal place.
SET @DecimalPlace = CHARINDEX( '.' , @MyNumber)
-- If we find decimal place...
IF @DecimalPlace > 0
BEGIN
-- Convert cents
SET @Temp = LEFT(SUBSTRING(@MyNumber, @DecimalPlace + 1,LEN(@MyNumber)) + '00', 2)
SET @Cents = dbo.ConvertTens(@Temp)
-- Strip off cents from remainder to convert.
SET @MyNumber = RTRIM(LEFT(@MyNumber, @DecimalPlace - 1))
END
SET @Count = 1
WHILE ISNULL(@MyNumber,'') <> ''
BEGIN
-- Convert last 3 digits of MyNumber to English dollars.
SET @Temp = dbo.ConvertHundreds(RIGHT(@MyNumber, 3))
IF ISNULL(@Temp,'') <> ''
BEGIN
SET @MultiplicationFactor =
CASE
WHEN @Count=2 THEN ' Thousand '
WHEN @Count=3 THEN ' Million '
WHEN @Count=4 THEN ' Billion '
WHEN @Count=5 THEN ' Trillion '
ELSE ''
END
SET @Dollars = ISNULL(@Temp,'') + @MultiplicationFactor + ISNULL(@Dollars,'')
END
IF LEN(@MyNumber) > 3
BEGIN
-- Remove last 3 converted digits from MyNumber.
SET @MyNumber = LEFT(@MyNumber, LEN(@MyNumber) - 3)
END
ELSE
BEGIN
SET @MyNumber = ''
END
SET @Count = @Count + 1
END
-- Clean up dollars.
SET @Dollars=
CASE
WHEN ISNULL(@Dollars,'') = '' THEN 'No Dollars'
WHEN ISNULL(@Dollars,'') = 'One' THEN 'One Dollar'
ELSE ISNULL(@Dollars,'') + ' Dollars'
END
-- Clean up cents.
SET @Cents=
CASE
WHEN @Cents = '' THEN ' And No Cents'
WHEN @Cents = 'One' THEN ' And One Cent'
ELSE ' And ' + ISNULL(@Cents,'') + ' Cents'
END
--@MultiplicationFactor
RETURN ISNULL(@Dollars,'') + ISNULL(@Cents,'')
END
GO
SELECT dbo.ConvertCurrencyToEnglish('2115600000.14'),'2115600000.14'
SELECT dbo.ConvertHundreds('787'),'787'
SELECT dbo.ConvertTens('87'),'87'
SELECT dbo.ConvertDigit('9') 
2007-10-02 (first published: 2002-06-20)
15,451 reads
I have always read that 8060 bytes is the maximum size. This is stated over and over again in Books Online, the MS site and numerous other sites, including this one. However a post in our forum recently questioned this. I decided to verify the problem and do a little research.
2007-10-02 (first published: 2002-02-07)
37,415 reads
The User_Defined_Functions.exe file contains the User-Defined Functions white paper. The User-Defined functions white paper outlines the characteristics of the new user-defined function (UDF) feature that is introduced in Microsoft SQL Server 2000. The white paper also summarizes how you can create your own Transact-SQL functions to extend the programmability of Transact-SQL.
2001-08-31
2,233 reads
The following stored procedure will demonstrate the use of cursor metadata. Using cursor metadata we can get information about an SQL statement and use that information to dynamically generate other code such as HTML or other stored procedures.
2001-07-27
5,204 reads
ne of the issues you face when building Web applications is handling the errors you encounter when interacting with a back-end database. I was recently working with someone to create a new Web site with SQL Server™, ActiveX® Data Objects (ADO), and ASP. Lots of little things came up that I thought were worth sharing with MIND readers, so I'll focus this column on what I learned from this experience and the solutions to many of the problems I faced.
2001-07-13
1,579 reads