查询临时表中的内容?(请高手帮忙)
hxnet 2006-07-01 10:57:23 ------------------------------------------------------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE zy_store_hzfymx --修改
@tjr char(4),@zyh char(10) --参数
AS
DECLARE @HZ_XM char(10),@BLH char(20),@ZYKS char(4),@CWH char(16),@zy_xjsf char(1)
IF NOT EXISTS(SELECT * FROM xt_code_dmb WHERE sybz='1') or (SELECT count(*) FROM xt_code_dmb WHERE sybz='1')>1
BEGIN
PRINT('xt_code_dmb表未维护,请先维护该表!')
RETURN
END
SET @zy_xjsf=(SELECT zy_xjsf FROM xt_code_dmb WHERE sybz='1')
SELECT @BLH =blh,@ZYKS=xzks FROM zy_cmain_hzzyqkb where zyh=@zyh
select @cwh = cwdm from zy_cmain_cwsyxx where zyh = @zyh
SELECT @HZ_XM =hz_xm FROM zy_cmain_hzjbqkb where blh = @BLH
CREATE TABLE [#hzfymx] (
[tjr] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[zyh] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[dh] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[jcxm] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[stxm] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[xmmc] [char] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[dw] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[gg] [char] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[sl] [int] NOT NULL ,
[ssje] [decimal](14, 3) NOT NULL ,
[stsj] [datetime] NOT NULL ,
[stry] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[ysdm] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[hdbz] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[tfbz] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[kjks] [char] (4) COLLATE Chinese_PRC_CI_AS NULL ,
[kjksmc] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[jcks] [char] (4) COLLATE Chinese_PRC_CI_AS NULL ,
[jcksmc] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[ypjcbz] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
)
delete from #hzfymx
--收费未取药处方--
INSERT INTO
--zy_main_hzfymx(tjr,zyh,dh,jcxm,stxm,xmmc,dw,gg,sl,ssje,stsj,stry,ysdm,hdbz,tfbz,ypjcbz)
#hzfymx(tjr,zyh,dh,jcxm,stxm,xmmc,dw,gg,sl,ssje,stsj,stry,ysdm,hdbz,tfbz,ypjcbz)
SELECT @tjr ,@zyh ,a.dh ,a.gkfldm ,b.ypdm ,c.ypmc,
case when d.bmbz ='mz' then c.mzdw else c.zydw end,
c.gg,
case when d.bmbz ='mz' then b.sl/c.mzbl else b.sl/c.zybl end,
case when d.bmbz ='mz' then b.lsj*b.sl else b.lsj*b.sl end,
sfsj,sfry,ysdm,'0' ,'0','yp'
FROM zy_cmain_cfsf_wqy a,
zy_cnext_cfxxb_wqy b,
code_ypdm c,
code_yf_kzyfdm d
WHERE a.zyh = @zyh
and a.dh = b.dh
and b.ypdm = c.ypdm
and a.kzyfdm = d.kzyfdm
and a.xjbz=(case when @zy_xjsf='1' then 0 else a.xjbz end )
--退费处方--
INSERT INTO
--zy_main_hzfymx(tjr,zyh,dh,jcxm,stxm,xmmc,dw,gg,sl,ssje,stsj,stry,ysdm,hdbz,tfbz,ypjcbz)
#hzfymx(tjr,zyh,dh,jcxm,stxm,xmmc,dw,gg,sl,ssje,stsj,stry,ysdm,hdbz,tfbz,ypjcbz)
SELECT @tjr,@zyh,c.dh,a.gkfldm,b.ypdm,d.ypmc,
case when e.bmbz ='mz' then d.mzdw else d.zydw end,
d.gg,
case when e.bmbz ='mz' then (0 - b.sl)/d.mzbl else (0 - b.sl)/d.zybl end,
case when e.bmbz ='mz' then 0 - b.lsj*b.sl else 0 - b.lsj*b.sl end,
c.tfsj,c.tfry,ysdm,'0','1','yp'
FROM zy_cmain_cfsf_wqy a,
zy_cnext_cfxxb_wqy b,
zy_cmain_cftf c,
code_ypdm d,
code_yf_kzyfdm e
WHERE a.zyh = @zyh
and a.dh = b.dh
and a.dh = c.dh
and b.ypdm = d.ypdm
and a.kzyfdm = e.kzyfdm
and a.xjbz=(case when @zy_xjsf='1' then 0 else a.xjbz end )
--发药处方--
INSERT INTO
--zy_main_hzfymx(tjr,zyh,dh,jcxm,stxm,xmmc,dw,gg,sl,ssje,stsj,stry,ysdm,hdbz,tfbz,ypjcbz)
#hzfymx(tjr,zyh,dh,jcxm,stxm,xmmc,dw,gg,sl,ssje,stsj,stry,ysdm,hdbz,tfbz,ypjcbz)
SELECT @tjr,@zyh,a.dh,a.gkfldm,b.ypdm,d.ypmc,
case when e.bmbz ='mz' then d.mzdw else d.zydw end,
d.gg,
case when e.bmbz ='mz' then b.sl/d.mzbl else b.sl/d.zybl end,
case when e.bmbz ='mz' then b.lsj*b.sl else b.lsj*b.sl end,
sfsj,sfry,ysdm,'1','0','yp'
FROM zy_cmain_cfsf_yqy a,
zy_cnext_cfxxb_yqy b,
zy_cmain_cffy c,
code_ypdm d,
code_yf_kzyfdm e
WHERE a.zyh = @zyh
and a.dh = b.dh
and a.dh = c.dh
and b.ypdm = d.ypdm
and a.kzyfdm = e.kzyfdm
and a.xjbz=(case when @zy_xjsf='1' then 0 else a.xjbz end )
--退费红方--
INSERT INTO
--zy_main_hzfymx(tjr,zyh,dh,jcxm,stxm,xmmc,dw,gg,sl,ssje,stsj,stry,ysdm,hdbz,tfbz,ypjcbz)
#hzfymx(tjr,zyh,dh,jcxm,stxm,xmmc,dw,gg,sl,ssje,stsj,stry,ysdm,hdbz,tfbz,ypjcbz)
SELECT @tjr,@zyh,c.dh,a.gkfldm,b.ypdm,d.ypmc,
case when f.bmbz ='mz' then d.mzdw else d.zydw end,
d.gg,
case when f.bmbz ='mz' then (0 - b.sl)/d.mzbl else (0 - b.sl)/d.zybl end,
case when f.bmbz ='mz' then 0 - b.lsj*b.sl else 0 - b.lsj*b.sl end,
c.tfsj,c.tfry,ysdm,'0','1','yp'
FROM zy_cmain_hflr a,
zy_cnext_hf b,
zy_cmain_hftf c,
code_ypdm d,
code_yf_kfdm e,
code_yf_kzyfdm f
WHERE a.zyh = @zyh
and a.dh = b.dh
and a.dh = c.dh
and b.ypdm = d.ypdm
and a.yfdm = e.kfdm
and e.kzyfdm = f.kzyfdm
and a.xjbz=(case when @zy_xjsf='1' then 0 else a.xjbz end )
--收费未核对检查单--
INSERT INTO
--zy_main_hzfymx(tjr,zyh,dh,jcxm,stxm,xmmc,dw,gg,sl,ssje,stsj,stry,ysdm,hdbz,tfbz,ypjcbz)
#hzfymx(tjr,zyh,dh,jcxm,stxm,xmmc,dw,gg,sl,ssje,stsj,stry,ysdm,hdbz,tfbz,ypjcbz)
SELECT @tjr,@zyh,a.dh,a.jcxm,c.mxxm,b.jcxmmc,b.dw,'',sl,sjdj*sl,sfsj,sfry,ysdm,'0','0','jc'
FROM zy_cmain_jcdxx a,
code_jcxmdm b,
zy_cnext_jcdxx c
WHERE zyh = @zyh
and b.jcxm =c.mxxm
and a.dh=c.dh
and a.xjbz=(case when @zy_xjsf='1' then 0 else a.xjbz end )
--退费检查单--
INSERT INTO
--zy_main_hzfymx(tjr,zyh,dh,jcxm,stxm,xmmc,dw,gg,sl,ssje,stsj,stry,ysdm,hdbz,tfbz,ypjcbz)
#hzfymx(tjr,zyh,dh,jcxm,stxm,xmmc,dw,gg,sl,ssje,stsj,stry,ysdm,hdbz,tfbz,ypjcbz)
SELECT @tjr,@zyh,b.dh,a.jcxm,d.mxxm,c.jcxmmc,c.dw,'',0 - sl,0 -sjdj*sl,b.tfsj,b.tfry,ysdm,'0','1','jc'
FROM zy_cmain_jcdxx a,
zy_cmain_jcdtf b,
code_jcxmdm c,
zy_cnext_jcdxx d
WHERE zyh = @zyh
and a.dh = b.dh
and c.jcxm = d.mxxm
and a.dh=d.dh
and a.xjbz=(case when @zy_xjsf='1' then 0 else a.xjbz end )
DELETE FROM zy_main_hzfymx WHERE tjr=@tjr --and zyh = @zyh
DELETE FROM zy_next_hzfymx WHERE tjr=@tjr
insert into zy_main_hzfymx(tjr,zyh,dh,jcxm,stxm,xmmc,dw,gg,sl,ssje,stsj,stry,ysdm,hdbz,tfbz,ypjcbz)
select tjr,zyh,dh,jcxm,stxm,xmmc,dw,gg,sl,ssje,stsj,stry,ysdm,hdbz,tfbz,ypjcbz
from #hzfymx
drop table #hzfymx
/****按发票项目结算*****/
INSERT INTO zy_next_hzfymx(tjr,zyh,sfxm,sfje)
SELECT @tjr,@zyh,zyfpdm,sum(ssje)
FROM zy_main_hzfymx a,
code_jcxmdm b
WHERE a.jcxm = b.jcxm
and zyh=@zyh
and tjr = @tjr
GROUP BY zyfpdm
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------------------------------------------------------------
这是一个医院管理系统软件中存储过程,需要使用ASP如何查询出临时表#hzfymx中的内容,上面是一个完成的存储过程,不能更改,因为其他的模块还需要调用它,不知该如何处理,请高手帮忙?