小写转大写金额[SQL SERVER]-数据库专栏,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