With this store procedure you can send a parameters like 2379.56 and it will return the word value "TWO THOUSAND THREE HUNDRED SEVENTY NINE AND 56/100".
2007-10-02 (first published: 2002-06-20)
15,443 reads
With this store procedure you can send a parameters like 2379.56 and it will return the word value "TWO THOUSAND THREE HUNDRED SEVENTY NINE AND 56/100".
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[sp_Num2Word]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE [dbo].[sp_Num2Word] GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO -- AUTOR: LUIS MARIA GUAYAN MVP VISUAL FOX PRO AND CLAUDIO ALABARCE -- DATE: 10-05-2002 CREATE PROCEDURE sp_Num2Word @Numero NUMERIC(20,2) AS BEGIN SET NOCOUNT ON DECLARE @lnEntero INT, @lcRetorno VARCHAR(512), @lnTerna INT, @lcMiles VARCHAR(512), @lcCadena VARCHAR(512), @lnUnidades INT, @lnDecenas INT, @lnCentenas INT, @lnFraccion INT SELECT @lnEntero = CAST(@Numero AS INT), @lnFraccion = (@Numero - @lnEntero) * 100, @lcRetorno = '', @lnTerna = 1 WHILE @lnEntero > 0 BEGIN /* WHILE */ -- Recorro terna por terna SELECT @lcCadena = '' SELECT @lnUnidades = @lnEntero % 10 SELECT @lnEntero = CAST(@lnEntero/10 AS INT) SELECT @lnDecenas = @lnEntero % 10 SELECT @lnEntero = CAST(@lnEntero/10 AS INT) SELECT @lnCentenas = @lnEntero % 10 SELECT @lnEntero = CAST(@lnEntero/10 AS INT) -- Analizo las unidades SELECT @lcCadena = CASE /* UNIDADES */ WHEN @lnUnidades = 1 THEN 'ONE ' + @lcCadena WHEN @lnUnidades = 2 THEN 'TWO ' + @lcCadena WHEN @lnUnidades = 3 THEN 'THREE ' + @lcCadena WHEN @lnUnidades = 4 THEN 'FOUR ' + @lcCadena WHEN @lnUnidades = 5 THEN 'FIVE ' + @lcCadena WHEN @lnUnidades = 6 THEN 'SIX ' + @lcCadena WHEN @lnUnidades = 7 THEN 'SEVEN ' + @lcCadena WHEN @lnUnidades = 8 THEN 'EIGHT ' + @lcCadena WHEN @lnUnidades = 9 THEN 'NINE ' + @lcCadena ELSE @lcCadena END /* UNIDADES */ -- Analizo las decenas SELECT @lcCadena = CASE /* DECENAS */ WHEN @lnDecenas = 1 THEN CASE @lnUnidades WHEN 0 THEN 'TEN ' WHEN 1 THEN 'ELEVEN ' WHEN 2 THEN 'TWELVE ' WHEN 3 THEN 'THIRTEEN ' WHEN 4 THEN 'FOURTEEN ' WHEN 5 THEN 'FIFTEEN ' WHEN 6 THEN 'SIXTEEN ' WHEN 7 THEN 'SEVENTEEN ' WHEN 8 THEN 'EIGHTEEN ' WHEN 9 THEN 'NINETEEN ' END WHEN @lnDecenas = 2 THEN 'TWENTY ' + @lcCadena WHEN @lnDecenas = 3 THEN 'THIRTY ' + @lcCadena WHEN @lnDecenas = 4 THEN 'FORTY ' + @lcCadena WHEN @lnDecenas = 5 THEN 'FIFTY ' + @lcCadena WHEN @lnDecenas = 6 THEN 'SIXTY ' + @lcCadena WHEN @lnDecenas = 7 THEN 'SEVENTY ' + @lcCadena WHEN @lnDecenas = 8 THEN 'EIGHTY ' + @lcCadena WHEN @lnDecenas = 9 THEN 'NINETY ' + @lcCadena ELSE @lcCadena END /* DECENAS */ -- Analizo las centenas SELECT @lcCadena = CASE /* CENTENAS */ WHEN @lnCentenas = 1 THEN 'ONE HUNDRED ' + @lcCadena WHEN @lnCentenas = 2 THEN 'TWO HUNDRED ' + @lcCadena WHEN @lnCentenas = 3 THEN 'THREE HUNDRED ' + @lcCadena WHEN @lnCentenas = 4 THEN 'FOUR HUNDRED ' + @lcCadena WHEN @lnCentenas = 5 THEN 'FIVE HUNDRED ' + @lcCadena WHEN @lnCentenas = 6 THEN 'SIX HUNDRED ' + @lcCadena WHEN @lnCentenas = 7 THEN 'SEVEN HUNDRED ' + @lcCadena WHEN @lnCentenas = 8 THEN 'EIGHT HUNDRED ' + @lcCadena WHEN @lnCentenas = 9 THEN 'NINE HUNDRED ' + @lcCadena ELSE @lcCadena END /* CENTENAS */ -- Analizo la terna SELECT @lcCadena = CASE /* TERNA */ WHEN @lnTerna = 1 THEN @lcCadena WHEN @lnTerna = 2 THEN @lcCadena + ' THOUSAND ' WHEN @lnTerna = 3 THEN @lcCadena + ' MILLON ' WHEN @lnTerna = 4 THEN @lcCadena + ' BILLON ' ELSE '' END /* TERNA */ -- Armo el retorno terna a terna SELECT @lcRetorno = @lcCadena + @lcRetorno SELECT @lnTerna = @lnTerna + 1 END /* WHILE */ IF @lnTerna = 1 SELECT @lcRetorno = 'ZERO' SELECT RTRIM(@lcRetorno) + ' AND ' + LTRIM(STR(@lnFraccion,2)) + '/100' END GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO