金额转人民币面值过程。 高手勿进,,免得浪费时间：）

[elfzzf]

CREATE PROCEDURE [dbo].[convert_money_par_value] ( @inmoney money) AS
begin
declare @money money --'
,@100num int
,@50num int
,@20num int
,@10num int
,@5num int
,@2num int
,@1num int
select @money=@inmoney
select @money=round(@money,0,1)

select @100num=round(@money/100,0,1), @money=@money-@100num*100
select @50num=round(@money/50,0,1), @money=@money-@50num*50
select @20num=round(@money/20,0,1), @money=@money-@20num*20
select @10num=round(@money/10,0,1), @money=@money-@10num*10
--
select @5num=round(@money/5,0,1), @money=@money-@5num*5
select @2num=round(@money/2,0,1), @money=@money-@2num*2
select @1num=round(@money/1,0,1), @money=@money-@1num*1


select @100num as '100元',@50num as '50元',@20num as '20元',@10num as '10元'
,@5num as '5元',@2num as '2元',@1num as '1元'

END
GO

--结果
-- exec convert_money_par_value 854
-- 100元 50元 20元 10元 5元 2元 1元
- 8 1 0 0 0 2 0
--金额转人民币面值

[txlicenhe]

不错。

[lynx1111]

不错。

[lynx1111]

谢谢！

[elfzzf]

哈哈，有星星捧场啊？ 快发年终红包了，

[elfzzf]

跟上面一样，只不过是函数
CREATE FUNCTION [dbo].[f_convert_money_parvalue] (@inmoney money)
RETURNS varchar(512) AS
BEGIN

declare @re_str varchar(512)
,@money money --'
,@100num int
,@50num int
,@20num int
,@10num int
,@5num int
,@2num int
,@1num int
select @money=@inmoney
select @money=round(@money,0,1)

select @100num=round(@money/100,0,1), @money=@money-@100num*100
select @50num=round(@money/50,0,1), @money=@money-@50num*50
select @20num=round(@money/20,0,1), @money=@money-@20num*20
select @10num=round(@money/10,0,1), @money=@money-@10num*10
--
select @5num=round(@money/5,0,1), @money=@money-@5num*5
select @2num=round(@money/2,0,1), @money=@money-@2num*2
select @1num=round(@money/1,0,1), @money=@money-@1num*1


--select @100num as '100元',@50num as '50元',@20num as '20元',@10num as '10元'
--,@5num as '5元',@2num as '2元',@1num as '1元'

select @re_str=
case when convert(varchar(128),@100num)>0 then '100元:'+convert(varchar(128),@100num) + '张 ' else '' end
+ case when convert(varchar(128),@50num)>0 then '50元:'+convert(varchar(128),@50num) + '张 ' else '' end
+ case when convert(varchar(128),@20num)>0 then '20元:'+ convert(varchar(128),@20num) + '张 ' else '' end
+ case when convert(varchar(128),@10num)>0 then '10元:'+convert(varchar(128),@10num) + '张 ' else '' end
+ case when convert(varchar(128),@5num)>0 then '5元:' +convert(varchar(128),@5num) + '张 ' else '' end
+ case when convert(varchar(128),@2num)>0 then '2元:'+convert(varchar(128),@2num) + '张 ' else '' end
+ case when convert(varchar(128),@1num)>0 then '1元:'+convert(varchar(128),@1num) + '张 ' else '' end
--select @re_str ='464641'
return @re_str



END

--select dbo.f_convert_money_parvalue( 65464)
--结果 100元:654张 50元:1张 10元:1张 2元:2张

[elfzzf]

修改默认值为 零 CREATE FUNCTION [dbo].[f_convert_money_parvalue] (@inmoney money=0)