小写转大写金额[SQL SERVER]

 原作在SQL 2000中有一些问题。修正错误并在SQL 2000中运行通过。

/********************************************************
作者：(wleii165@yahoo.com)
版本：1.0
创建时间：20020227
修改时间：
功能：小写金额转换成大写
参数：n_LowerMoney 小写金额
          v_TransType 种类 -- 1: directly translate, 0: read it in words
输出：大写金额
********************************************************/
CREATE PROCEDURE dbo.L2U
(
@n_LowerMoney numeric(15,2),
@v_TransType int,
@RET VARCHAR(200) output
)
 AS
 
Declare @v_LowerStr VARCHAR(200) -- 小写金额
Declare @v_UpperPart VARCHAR(200)
Declare @v_UpperStr VARCHAR(200) -- 大写金额
Declare @i_I int

set nocount on

select @v_LowerStr = LTRIM(RTRIM(STR(@n_LowerMoney,20,2))) --四舍五入为指定的精度并删除数据左右空格

select @i_I = 1
select @v_UpperStr = @#@#

while ( @i_I <= len(@v_LowerStr))
begin
      select @v_UpperPart = case substring(@v_LowerStr,len(@v_LowerStr) - @i_I + 1,1)
                            WHEN  @#.@# THEN  @#元@#
                            WHEN  @#0@# THEN  @#零@#
                            WHEN  @#1@# THEN  @#壹@#
                            WHEN  @#2@# THEN  @#贰@#
                            WHEN  @#3@# THEN  @#叁@#
                            WHEN  @#4@# THEN  @#肆@#
                            WHEN  @#5@# THEN  @#伍@#
                            WHEN  @#6@# THEN  @#陆@#
                            WHEN  @#7@# THEN  @#柒@#
                            WHEN  @#8@# THEN  @#捌@#
                            WHEN  @#9@# THEN  @#玖@#
                            END
                          +
                            case @i_I
                            WHEN  1  THEN  @#分@#
                            WHEN  2  THEN  @#角@#
                            WHEN  3  THEN  @#@#
                            WHEN  4  THEN  @#@#
                            WHEN  5  THEN  @#拾@#
                            WHEN  6  THEN  @#佰@#
                            WHEN  7  THEN  @#仟@#
                            WHEN  8  THEN  @#万@#
                            WHEN  9  THEN  @#拾@#
                            WHEN  10  THEN  @#佰@#
                            WHEN  11  THEN  @#仟@#
                            WHEN  12  THEN  @#亿@#
                            WHEN  13  THEN  @#拾@#
                            WHEN  14  THEN  @#佰@#
                            WHEN  15  THEN  @#仟@#
                            WHEN  16  THEN  @#万@#
                            ELSE @#@#
                            END
select @v_UpperStr = @v_UpperPart + @v_UpperStr
select @i_I = @i_I + 1
end

--------print  @#//v_UpperStr =@#+@v_UpperStr +@#//@#

if ( @v_TransType=0 )
begin
select @v_UpperStr = REPLACE(@v_UpperStr,@#零拾@#,@#零@#)
select @v_UpperStr = REPLACE(@v_UpperStr,@#零佰@#,@#零@#)
select @v_UpperStr = REPLACE(@v_UpperStr,@#零仟@#,@#零@#)
select @v_UpperStr = REPLACE(@v_UpperStr,@#零零零@#,@#零@#)
select @v_UpperStr = REPLACE(@v_UpperStr,@#零零@#,@#零@#)
select @v_UpperStr = REPLACE(@v_UpperStr,@#零角零分@#,@#整@#)
select @v_UpperStr = REPLACE(@v_UpperStr,@#零分@#,@#整@#)
select @v_UpperStr = REPLACE(@v_UpperStr,@#零角@#,@#零@#)
select @v_UpperStr = REPLACE(@v_UpperStr,@#零亿零万零元@#,@#亿元@#)
select @v_UpperStr = REPLACE(@v_UpperStr,@#亿零万零元@#,@#亿元@#)
select @v_UpperStr = REPLACE(@v_UpperStr,@#零亿零万@#,@#亿@#)
select @v_UpperStr = REPLACE(@v_UpperStr,@#零万零元@#,@#万元@#)
select @v_UpperStr = REPLACE(@v_UpperStr,@#万零元@#,@#万元@#)
select @v_UpperStr = REPLACE(@v_UpperStr,@#零亿@#,@#亿@#)
select @v_UpperStr = REPLACE(@v_UpperStr,@#零万@#,@#万@#)
select @v_UpperStr = REPLACE(@v_UpperStr,@#零元@#,@#元@#)
select @v_UpperStr = REPLACE(@v_UpperStr,@#零零@#,@#零@#)
end

-- 对壹元以下的金额的处理
if ( substring(@v_UpperStr,1,1)=@#元@# )
begin
     select @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
end

if (substring(@v_UpperStr,1,1)= @#零@#)
begin
     select @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
end

if (substring(@v_UpperStr,1,1)=@#角@#)
begin
     select @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
end

if ( substring(@v_UpperStr,1,1)=@#分@#)
begin
     select @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
end

if (substring(@v_UpperStr,1,1)=@#整@#)
begin
     select @v_UpperStr = @#零元整@#
end

select @ret=@v_UpperStr

GO

调用过程：

declare @ret varchar(200)

exec L2U 567983.897,1,@ret output

select @ret

原文转自：http://www.ltesting.net