目前有这样的问题,有处方报表,打印格式为A5的纸张大小,但有些药品名称比较长。而报表上要显示的字段比较多,药品名称长了后有打印不小了,需要把药品名称分成两行来显示才行,但分成两行后后面要显示的数量、用法等到打印到下一行了。有没有办法来解决这个问题呢。下面是我写的函数:
CREATE function dbo.get_mzsf_order_detail(@p_id varchar(12)
,@times varchar(2)
,@order int
,@group_no varchar(6))
returns varchar(8000)
as
begin
declare
@print_name char(50)
,@self_flag varchar(2)
,@order_type char(2)
,@freq_name char(8)
,@supply_name char(10)
,@dosage char(15)
,@item_no smallint
,@parent_no int
,@retprice char(10)
,@comment varchar(100)
,@charge_amount varchar(6)
,@allname varchar(2000)
,@i int
,@samp_id varchar(14)
,@xm1 varchar(1000)
,@xm2 varchar(2000)
,@xm3 varchar(200)
,@xm4 varchar(200)
,@xm5 varchar(200)
,@tmp varchar(2000)
set @allname=''
set @i=1
set @i=1
set @xm1 = ''
set @xm2 = ''
set @xm3 =''
set @xm4= ''
set @xm5= ''
declare detail cursor for--药品
select distinct
----ltrim(rtrim( g.print_name )) +'('+ ltrim(rtrim( g.specification ))+')'+'('+ltrim(rtrim(q.name))+')'+'('+ltrim(rtrim(round(g.retprice,2)))+'元'+')' print_name,
-----convert(char(45),ltrim(rtrim( g.print_name )) +'('+ ltrim(rtrim( g.specification ))+')'+'('+ltrim(rtrim(q.name))+')'+'('+ltrim(rtrim(round(g.retprice,2)))+'元'+')' ,121) print_name,
case when len(ltrim(rtrim( g.print_name )))>14 then g.print_name+char(13)+'('+ ltrim(rtrim( g.specification ))+')'+'('+ltrim(rtrim(q.name))+')'+'('+ltrim(rtrim(round(g.retprice,2)))+'元'+')'
else ltrim(rtrim( g.print_name )) +'('+ ltrim(rtrim( g.specification ))+')'+'('+ltrim(rtrim(q.name))+')'+'('+ltrim(rtrim(round(g.retprice,2)))+'元'+')' end print_name,
-- ltrim(rtrim( g.print_name )) print_name, self_flag =case j.charge_group when '02' then '②' when '01' then '③' else '①' end,
case when g.drug_flag='0' and j.order_type in('15','16','14','13','12','10') and f.visit_dept<>'1070101' then '门诊'
when j.order_type='11' and f.visit_dept<>'1070101' then '急诊'
when g.drug_flag in('1','2') and f.visit_dept<>'1070101' then '麻、精一'
when g.drug_flag='3'and f.visit_dept<>'1070101' then '精二'
when j.order_type='20' and f.visit_dept<>'1070101' and charge_no<0 then '自备药'
when j.order_type='20' and f.visit_dept<>'1070101' and j.charge_no>0 then '检查药'
when f.visit_dept='1070101' then '儿科处方' else '' end order_type,
isnull(h.name,'') freq_name,
j.item_no,j.parent_no,
isnull(j.comment,'') comment,
ltrim(rtrim(isnull( i.supply_name,''))) supply_name,
cast(isnull(j.dosage,'1') as varchar(10))+cast(isnull(n.name,'') as varchar(10))+'/次' dosage,
rtrim(ltrim(convert(char(2),j.charge_amount)))+rtrim(ltrim(m.name)) charge_amount
from
mz_visit_table f,yp_dict g,
yz_frequency h,mz_detail_charge j,yz_supply i,yp_unit m,yp_unit n,zd_common_reason p,yp_dosage q
where f.patient_id=j.patient_id
and f.times=j.times
and g.charge_code=j.charge_code
and g.serial=j.serial_no
and j.freq_code*=h.code
and g.pack_unit*=m.code
and j.dosage_unit*=n.code
and g.dosage*=q.code
and j.supply_code*=i.supply_code
and j.fit_type*=p.reason_id
and j.patient_id like @p_id
and j.times=@times
and j.group_no= @group_no
and j.order_no=@order
and j.ledger_sn=0
order by j.item_no,j.parent_no
open detail
set @tmp = ''fetch next from detail
into @print_name,@self_flag,@order_type,@freq_name,@item_no,@parent_no,@comment,@supply_name,@dosage,@charge_amount
while @@fetch_status=0
begin
set @print_name =Left( @print_name + space(200), 70)
set @freq_name =Left( @freq_name + space(200),20)
set @charge_amount = Left( @charge_amount + space(10),20)
--- set @comment=char(13)+isnull('备注:'+@comment,'')
if charindex('(',@supply_name)<>0
begin
set @supply_name =Left( substring(@supply_name,0,charindex('(',@supply_name)),10)
end
else
begin
set @supply_name =Left( @supply_name + space(200),10)
end
if (isnull(rtrim(ltrim(@comment)),'')='')
begin
set @comment=char(13)+isnull(@comment,'')
end
else
begin
set @comment=char(13)+space(2)+isnull('备注:'+@comment,'')
end
set @tmp= @tmp+isnull(@self_flag,'')+isnull(@print_name,'') + space(5)+isnull(@charge_amount,'')+isnull(@freq_name,'')+char(255)+isnull(@dosage,'') +isnull(@supply_name,'')+isnull(@comment,'')+char(13)fetch next from detail
into @print_name,@self_flag,@order_type,@freq_name,@item_no,@parent_no,@comment,@supply_name,@dosage,@charge_amount
end
close detail
deallocate detail return @tmpend
就是希望药品名称如果比较长就分行显示后面的规格、剂型,但后面的数量频率。用法还是在原先的药品名称的行后显示。希望各位大哥帮忙哦,先谢谢了。
CREATE function dbo.get_mzsf_order_detail(@p_id varchar(12)
,@times varchar(2)
,@order int
,@group_no varchar(6))
returns varchar(8000)
as
begin
declare
@print_name char(50)
,@self_flag varchar(2)
,@order_type char(2)
,@freq_name char(8)
,@supply_name char(10)
,@dosage char(15)
,@item_no smallint
,@parent_no int
,@retprice char(10)
,@comment varchar(100)
,@charge_amount varchar(6)
,@allname varchar(2000)
,@i int
,@samp_id varchar(14)
,@xm1 varchar(1000)
,@xm2 varchar(2000)
,@xm3 varchar(200)
,@xm4 varchar(200)
,@xm5 varchar(200)
,@tmp varchar(2000)
set @allname=''
set @i=1
set @i=1
set @xm1 = ''
set @xm2 = ''
set @xm3 =''
set @xm4= ''
set @xm5= ''
declare detail cursor for--药品
select distinct
----ltrim(rtrim( g.print_name )) +'('+ ltrim(rtrim( g.specification ))+')'+'('+ltrim(rtrim(q.name))+')'+'('+ltrim(rtrim(round(g.retprice,2)))+'元'+')' print_name,
-----convert(char(45),ltrim(rtrim( g.print_name )) +'('+ ltrim(rtrim( g.specification ))+')'+'('+ltrim(rtrim(q.name))+')'+'('+ltrim(rtrim(round(g.retprice,2)))+'元'+')' ,121) print_name,
case when len(ltrim(rtrim( g.print_name )))>14 then g.print_name+char(13)+'('+ ltrim(rtrim( g.specification ))+')'+'('+ltrim(rtrim(q.name))+')'+'('+ltrim(rtrim(round(g.retprice,2)))+'元'+')'
else ltrim(rtrim( g.print_name )) +'('+ ltrim(rtrim( g.specification ))+')'+'('+ltrim(rtrim(q.name))+')'+'('+ltrim(rtrim(round(g.retprice,2)))+'元'+')' end print_name,
-- ltrim(rtrim( g.print_name )) print_name, self_flag =case j.charge_group when '02' then '②' when '01' then '③' else '①' end,
case when g.drug_flag='0' and j.order_type in('15','16','14','13','12','10') and f.visit_dept<>'1070101' then '门诊'
when j.order_type='11' and f.visit_dept<>'1070101' then '急诊'
when g.drug_flag in('1','2') and f.visit_dept<>'1070101' then '麻、精一'
when g.drug_flag='3'and f.visit_dept<>'1070101' then '精二'
when j.order_type='20' and f.visit_dept<>'1070101' and charge_no<0 then '自备药'
when j.order_type='20' and f.visit_dept<>'1070101' and j.charge_no>0 then '检查药'
when f.visit_dept='1070101' then '儿科处方' else '' end order_type,
isnull(h.name,'') freq_name,
j.item_no,j.parent_no,
isnull(j.comment,'') comment,
ltrim(rtrim(isnull( i.supply_name,''))) supply_name,
cast(isnull(j.dosage,'1') as varchar(10))+cast(isnull(n.name,'') as varchar(10))+'/次' dosage,
rtrim(ltrim(convert(char(2),j.charge_amount)))+rtrim(ltrim(m.name)) charge_amount
from
mz_visit_table f,yp_dict g,
yz_frequency h,mz_detail_charge j,yz_supply i,yp_unit m,yp_unit n,zd_common_reason p,yp_dosage q
where f.patient_id=j.patient_id
and f.times=j.times
and g.charge_code=j.charge_code
and g.serial=j.serial_no
and j.freq_code*=h.code
and g.pack_unit*=m.code
and j.dosage_unit*=n.code
and g.dosage*=q.code
and j.supply_code*=i.supply_code
and j.fit_type*=p.reason_id
and j.patient_id like @p_id
and j.times=@times
and j.group_no= @group_no
and j.order_no=@order
and j.ledger_sn=0
order by j.item_no,j.parent_no
open detail
set @tmp = ''fetch next from detail
into @print_name,@self_flag,@order_type,@freq_name,@item_no,@parent_no,@comment,@supply_name,@dosage,@charge_amount
while @@fetch_status=0
begin
set @print_name =Left( @print_name + space(200), 70)
set @freq_name =Left( @freq_name + space(200),20)
set @charge_amount = Left( @charge_amount + space(10),20)
--- set @comment=char(13)+isnull('备注:'+@comment,'')
if charindex('(',@supply_name)<>0
begin
set @supply_name =Left( substring(@supply_name,0,charindex('(',@supply_name)),10)
end
else
begin
set @supply_name =Left( @supply_name + space(200),10)
end
if (isnull(rtrim(ltrim(@comment)),'')='')
begin
set @comment=char(13)+isnull(@comment,'')
end
else
begin
set @comment=char(13)+space(2)+isnull('备注:'+@comment,'')
end
set @tmp= @tmp+isnull(@self_flag,'')+isnull(@print_name,'') + space(5)+isnull(@charge_amount,'')+isnull(@freq_name,'')+char(255)+isnull(@dosage,'') +isnull(@supply_name,'')+isnull(@comment,'')+char(13)fetch next from detail
into @print_name,@self_flag,@order_type,@freq_name,@item_no,@parent_no,@comment,@supply_name,@dosage,@charge_amount
end
close detail
deallocate detail return @tmpend
就是希望药品名称如果比较长就分行显示后面的规格、剂型,但后面的数量频率。用法还是在原先的药品名称的行后显示。希望各位大哥帮忙哦,先谢谢了。
解决方案 »
- sql server 2000里面的数据如何转换,并且写入表?
- SQL Server Configuration Manager sql server 服务 远程过程调用失败。
- 新手问题,在一个存储过程执行两个SQL语句的问题
- 数据文件为*.MDF,如何建立MS sqlserver服务
- 帮我把 这段存储过程还成sql2000能用的
- 有几个项目,钱钱多多,时间少少。找人合作!
- 这句sql access下没问题,sql server下就报错???
- 运行第一步时出现一个提示:ADOQUERY1:COMMANDTEXT does not reture result set! 怎么让这个提示不影响以后的程序运行?
- sql数据库升级的问题
- 为什么我没有办法访问自己在SQL SERVER中创建的表?
- 在SQL 2000中创建触发器
- 请教存储过程中返回游标的问题
SQL SERVER实现起来好麻烦.
再打印
right(药品名,len(药品名)-10)再打印下一种药品。反正处方的药品会太多,用游标来处理也不会显得慢。