create function SF_PE_GetSQXMJE --alter (修改函数)
(
@PTJH000 int,
@PJCKSID int-- =0 设置默认值为0
)
returns NUMERIC(8,2)
as
BEGIN
declare @VYSJE00 NUMERIC(8,2)=0;--原始金额
declare @VHZJE00 NUMERIC(8,2);--会诊金额
declare @VZJE000 NUMERIC(8,2);--总金额
declare @ysje NUMERIC(8,2);
begin
declare CUR_SQXM CURSOR for
select YSJE00 from PE_YWSJ_TJYW00 A
where TJH000 = @PTJH000 and JCBZ00 = '1' and JCKSID = @PJCKSID;
open CUR_SQXM
fetch next from CUR_SQXM into @ysje --初始必要
while @@FETCH_STATUS=0 --没数据时候推出循环
begin
begin
if @ysje is not null
set @VYSJE00 = @VYSJE00+@ysje
end
fetch next from CUR_SQXM into @ysje
end
close CUR_SQXM
deallocate CUR_SQXM
end
select @VHZJE00=HZJE00, @VZJE000=ZJR000 from PE_YWSJ_KSHZYS where TJH000 = @PTJH000 and BMBH00 = @PJCKSID and SFSHYS = '1';
if @VHZJE00 is null set @VHZJE00=0;
if @VZJE000 is null set @VZJE000=0;
set @VHZJE00 = @VZJE000 - @VHZJE00;
set @VYSJE00 = @VYSJE00 - @VHZJE00;
return @VYSJE00;
END
=========================调试====================================
BEGIN
declare @VYSJE00 NUMERIC(8,2)=0;--原始金额
declare @VHZJE00 NUMERIC(8,2);--会诊金额
declare @VZJE000 NUMERIC(8,2);--总金额
declare @ysje NUMERIC(8,2);
declare @PTJH000 int=2011659;--入参
declare @PJCKSID int =9--入参
begin
declare CUR_SQXM CURSOR for
select YSJE00 from PE_YWSJ_TJYW00 A
where TJH000 = @PTJH000 and JCBZ00 = '1' and JCKSID = @PJCKSID;
open CUR_SQXM
fetch next from CUR_SQXM into @ysje --初始必要
while @@FETCH_STATUS=0
begin
begin
if @ysje is not null
set @VYSJE00 = @VYSJE00+@ysje
end
fetch next from CUR_SQXM into @ysje
end
close CUR_SQXM
deallocate CUR_SQXM
end
select @VHZJE00=HZJE00, @VZJE000=ZJR000 from PE_YWSJ_KSHZYS where TJH000 = @PTJH000 and BMBH00 = @PJCKSID and SFSHYS = '1';
if @VHZJE00 is null set @VHZJE00=0;
if @VZJE000 is null set @VZJE000=0;
set @VHZJE00 = @VZJE000 - @VHZJE00;
set @VYSJE00 = @VYSJE00 - @VHZJE00;
begin select @VZJE000 as je end
begin select @VYSJE00 as je end
END
===========================================另一个函数=======================================
ALTER FUNCTION [dbo].[SF_PE_GetXMMS]
(
@PTJH000 int,
@PBMBH00 int,
@POPFLAG int
)
returns VARCHAR(4000)
as
begin
declare @VCOUNT0 int=0;
declare @VRESULT varchar(4000)=null;
declare CUR_TJJCXM CURSOR for
select distinct rtrim(ltrim(TZCMS0)) TZCMS0 from PE_YWSJ_TJJCXM A where TJH000 = @PTJH000 and BMBH00 = @PBMBH00
and not exists (select 1 from PE_YWSJ_TJYW00 where TJH000 = A.TJH000 and SFYWID = A.SFYWID and TFNTBZ != '0');
declare CUR_YXJCXM CURSOR for
select JCXMMC, TZCMS0 from PE_YWSJ_TJJCXM A where TJH000 = @PTJH000 and BMBH00 = @PBMBH00 and TZCMS0 is not null
and not exists (select 1 from PE_YWSJ_TJYW00 where TJH000 = A.TJH000 and SFYWID = A.SFYWID and TFNTBZ != '0') and isnull(ZZJB00,0) != 0;
declare @TZCMS0 varchar(3000);
declare @JCXMMC VARCHAR(3000);
if @POPFLAG=1
begin
open CUR_YXJCXM
fetch next from CUR_YXJCXM into @JCXMMC,@TZCMS0
while @@FETCH_STATUS=0
begin
set @VCOUNT0 = @VCOUNT0+1;
BEGIN
IF @VRESULT is null
SET @VRESULT = '('+cast(@VCOUNT0 as VARCHAR)+')'+rtrim(LTRIM(@JCXMMC))+':'+rtrim(ltrim(@TZCMS0))
ELSE
SET @VRESULT = @VRESULT+char(13) +'('+cast(@VCOUNT0 as VARCHAR)+')'+rtrim(LTRIM(@JCXMMC))+':'+rtrim(ltrim(@TZCMS0))
END
fetch next from CUR_YXJCXM into @JCXMMC,@TZCMS0
end
close CUR_YXJCXM
deallocate CUR_YXJCXM
end
ELSE
begin
open CUR_TJJCXM
fetch next from CUR_TJJCXM into @TZCMS0
while @@FETCH_STATUS=0
begin
set @VCOUNT0 = @VCOUNT0+1;
BEGIN
IF @VRESULT is null
SET @VRESULT = '('+cast(@VCOUNT0 as VARCHAR)+')'+rtrim(ltrim(@TZCMS0))
ELSE
SET @VRESULT = @VRESULT+char(13) +'('+cast(@VCOUNT0 as VARCHAR)+')'+rtrim(ltrim(@TZCMS0))
END
fetch next from CUR_TJJCXM into @TZCMS0
end
close CUR_TJJCXM
deallocate CUR_TJJCXM
end
return @VRESULT;
end
SqlServer自定义函数
最新推荐文章于 2024-02-13 00:45:51 发布