SSRS - How to convert Numbers to Words in SQL for use with SSRS

This can be achieved by creating a lookup table, and a function to convert numbers to words.

Original Post
http://stackoverflow.com/questions/1673265/how-to-write-number-to-word-function-in-sql-server

--Create conversion table
CREATE TABLE [dbo].[BI_Sequence]
    (
      seq INTEGER NOT NULL UNIQUE,
      word [varchar](25) NOT NULL
    )

INSERT INTO [BI_Sequence] SELECT 0, ''
INSERT INTO [BI_Sequence] SELECT 1, 'One'
INSERT INTO [BI_Sequence] SELECT 2, 'Two'
INSERT INTO [BI_Sequence] SELECT 3, 'Three'
INSERT INTO [BI_Sequence] SELECT 4, 'Four'
INSERT INTO [BI_Sequence] SELECT 5, 'Five'
INSERT INTO [BI_Sequence] SELECT 6, 'Six'
INSERT INTO [BI_Sequence] SELECT 7, 'Seven'
INSERT INTO [BI_Sequence] SELECT 8, 'Eight'
INSERT INTO [BI_Sequence] SELECT 9, 'Nine'

INSERT INTO [BI_Sequence] SELECT 10, 'Ten'
INSERT INTO [BI_Sequence] SELECT 11, 'Eleven'
INSERT INTO [BI_Sequence] SELECT 12, 'Twelve'
INSERT INTO [BI_Sequence] SELECT 13, 'Thirteen'
INSERT INTO [BI_Sequence] SELECT 14, 'Fourteen'
INSERT INTO [BI_Sequence] SELECT 15, 'Fifteen'
INSERT INTO [BI_Sequence] SELECT 16, 'Sixteen'
INSERT INTO [BI_Sequence] SELECT 17, 'Seventeen'
INSERT INTO [BI_Sequence] SELECT 18, 'Eighteen'
INSERT INTO [BI_Sequence] SELECT 19, 'Nineteen'

INSERT INTO [BI_Sequence] SELECT 20, 'Twenty'
INSERT INTO [BI_Sequence] SELECT 30, 'Thirty'
INSERT INTO [BI_Sequence] SELECT 40, 'Forty'
INSERT INTO [BI_Sequence] SELECT 50, 'Fifty'
INSERT INTO [BI_Sequence] SELECT 60, 'Sixty'
INSERT INTO [BI_Sequence] SELECT 70, 'Seventy'
INSERT INTO [BI_Sequence] SELECT 80, 'Eighty'
INSERT INTO [BI_Sequence] SELECT 90, 'Ninety'

--Create Function
create FUNCTION dbo.BI_NumToWords (
                @num AS INTEGER
)       RETURNS VARCHAR(50)
AS
BEGIN

DECLARE @words AS VARCHAR(50)

IF      @num =     0 SELECT @words = 'Zero'
ELSE IF @num <    20 SELECT @words = word FROM BI_Sequence WHERE seq = @num
ELSE IF @num <   100 (SELECT @words = TTens.word + ' ' + TUnits.word
                      FROM BI_Sequence AS TUnits
                     CROSS JOIN BI_Sequence AS TTens
                     WHERE TUnits.seq = (@num % 100) % 10
                       AND TTens.seq = (@num % 100) - (@num % 100) % 10
                    )
ELSE IF @num =   100 (SELECT @words = THundreds.word + ' Hundred'
                      FROM BI_Sequence AS THundreds
                     WHERE THundreds.seq = (@num / 100)
                    )
ELSE IF @num <  1000 (
    SELECT @words = THundreds.word + ' Hundred and '
                   + TTens.word + ' ' + TUnits.word
                      FROM BI_Sequence AS TUnits
                     CROSS JOIN BI_Sequence AS TTens
                     CROSS JOIN BI_Sequence AS THundreds
                     WHERE TUnits.seq = (@num % 100) % 10
                       AND TTens.seq = (@num % 100) - (@num % 100) % 10
                       AND THundreds.seq = (@num / 100)
                    )
ELSE IF @num =  1000 (SELECT @words = TThousand.word + ' Thousand'
                      FROM BI_Sequence AS TThousand
                     WHERE TThousand.seq = (@num / 1000)
                    )
ELSE IF @num < 10000 (
    SELECT @words = TThousand.word + ' Thousand '
                   + THundreds.word + ' Hundred and '
                   + TTens.word + ' ' + TUnits.word
                      FROM BI_Sequence AS TUnits
                     CROSS JOIN BI_Sequence AS TTens
                     CROSS JOIN BI_Sequence AS THundreds
                     CROSS JOIN BI_Sequence AS TThousand
                     WHERE TUnits.seq = (@num % 100) % 10
                       AND TTens.seq = (@num % 100) - (@num % 100) % 10
                       AND THundreds.seq = (@num / 100) - (@num / 1000) * 10
                       AND TThousand.seq = (@num / 1000)
                    )
ELSE SELECT @words = STR(@num)

RETURN @words

END

--Call the function by using
--Select dbo.BI_NumtoWords(9)
--Where 9 is any number