小写转大写金额[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

调用过程：

declare @ret varchar(200)

exec L2U 567983.897,1,@ret output

select @ret