insert into #f_jjbk(编号,单位名称,项目名称,渠道名称,本币金额,说明,经办人,拨款日期,开户银行,帐号,附件,拨款性质,批准文号) select 编号,单位名称,项目名称,渠道名称,convert(varchar(50),convert(money,本币金额),5),说明,经办人,拨款日期,开户银行,帐号,附件,拨款性质,批准文号 from f_jjbkgl,f_dw,f_jjxm,f_zjqd,f_yh where 编号=@bh and f_jjbkgl.收款dwid=f_dw.[id] and f_jjbkgl.项目id=f_jjxm.id and qdid=f_zjqd.id and f_jjbkgl.收款单位银行id=f_yh.id ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------10 declare @lenth int, @i int, @str_unit varchar(50), @str_dw varchar(50), @str_zong varchar(50), @str_je varchar(50) set @str_zong='' select @str_je=本币金额 from #f_jjbk set @lenth = Len(@str_je)set @i=1 -------------------20 while @i <= @lenth begin set @str_unit=right(@str_je,@i) set @str_unit = Left(@str_unit, 1) If @str_unit <> '.' And @str_unit <> ',' begin
set @str_unit= Case @str_unit when '0' then '零' ------------------------------30 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 '玖' ----------------------------------40 End
set @str_dw= Case @i when 1 then '分整' when 2 then '角' when 3 then '' when 4 then '元' when 5 then '拾' when 6 then '佰' when 7 then '' ------------------------------------50 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 '拾' when 17 then '佰'
when 18 then '仟' End set @str_zong = @str_unit + @str_dw + @str_zong end set @i=@i+1 end update #f_jjbk set dx=@str_zong select * from #f_jjbk GO
写了一个存储过程,将要求的分组信息填进一张虚拟表,再把表内容检索出来做为数据源赋
予DataReport.datasource就行了.
@bh varchar(50)
AS
create table #f_jjbk(编号 varchar(50),单位名称 varchar(50),项目名称 varchar(50),渠道名称 varchar(50),本币金额 varchar(50),dx varchar(50),说明 varchar(200),
经办人 varchar(50),拨款日期 datetime,开户银行 varchar(50),帐号 varchar(50),附件 int,拨款性质 varchar(50),批准文号 varchar(50))
insert into #f_jjbk(编号,单位名称,项目名称,渠道名称,本币金额,说明,经办人,拨款日期,开户银行,帐号,附件,拨款性质,批准文号)
select 编号,单位名称,项目名称,渠道名称,convert(varchar(50),convert(money,本币金额),5),说明,经办人,拨款日期,开户银行,帐号,附件,拨款性质,批准文号
from f_jjbkgl,f_dw,f_jjxm,f_zjqd,f_yh
where 编号=@bh and f_jjbkgl.收款dwid=f_dw.[id] and f_jjbkgl.项目id=f_jjxm.id and qdid=f_zjqd.id and f_jjbkgl.收款单位银行id=f_yh.id
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------10
declare @lenth int,
@i int,
@str_unit varchar(50),
@str_dw varchar(50),
@str_zong varchar(50),
@str_je varchar(50)
set @str_zong=''
select @str_je=本币金额 from #f_jjbk
set @lenth = Len(@str_je)set @i=1 -------------------20
while @i <= @lenth
begin
set @str_unit=right(@str_je,@i)
set @str_unit = Left(@str_unit, 1) If @str_unit <> '.' And @str_unit <> ','
begin
set @str_unit=
Case @str_unit
when '0' then '零'
------------------------------30
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 '玖' ----------------------------------40
End
set @str_dw=
Case @i
when 1 then '分整'
when 2 then '角'
when 3 then ''
when 4 then '元'
when 5 then '拾'
when 6 then '佰'
when 7 then '' ------------------------------------50
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 '拾'
when 17 then '佰'
when 18 then '仟'
End
set @str_zong = @str_unit + @str_dw + @str_zong
end
set @i=@i+1 end
update #f_jjbk set dx=@str_zong
select * from #f_jjbk
GO