初级存储过程（游标）
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
---先传1，2，3，4
ALTER PROCEDURE [dbo].[cw_MedicalDatalist121]
@subj_code varchar(20), ---分类ID号,
@start_time nvarchar(50), ---开始时间
@end_time nvarchar(50) ---结束时间
AS
BEGIN
declare @name varchar(20),@code nvarchar(50),@amount Numeric(38)
if(@start_time is null and @end_time is null)
begin
if exists (select * from cw_subject where (LEFT(subj_code, LEN(@subj_code)) = @subj_code) and
(LEN(subj_code) = LEN(@subj_code) + 1))
begin
select subj_code,subj_name,0 as amount into #result
from cw_subject
where (LEFT(subj_code, LEN(@subj_code)) = @subj_code) and
(LEN(subj_code) = LEN(@subj_code) + 1)
order by subj_code;
DECLARE cw_subject_view CURSOR FOR
select * from #result
OPEN cw_subject_view;
fetch next from cw_subject_view into @code,@name,@amount
while(@@fetch_status=0)
begin
select @amount=sum(amount) from cw_MedicalData
where (LEFT(subj_code, LEN(@code)) = @code)
if(@amount is not null)
begin
update #result set amount =@amount where subj_code=@code
end
fetch next from cw_subject_view into @code,@name,@amount
end
close cw_subject_view
deallocate cw_subject_view
select * from #result;
drop table #result;
end
else
begin
if exists (select * from cw_MedicalData where subj_code=@subj_code)
begin
select a.subj_code,a.subj_name,b.amount,b.tabler,b.summary
from cw_subject a,cw_MedicalData b
where a.subj_code=b.subj_code and a.subj_code=@subj_code
end
else
begin
select subj_code,subj_name,0 as amount,null as tabler,null as summary
from cw_subject
where subj_code=@subj_code
end
end
end
else
begin
if exists (select * from cw_subject where (LEFT(subj_code, LEN(@subj_code)) = @subj_code) and
(LEN(subj_code) = LEN(@subj_code) + 1))
begin
select subj_code,subj_name,0 as amount into #result2
from cw_subject
where (LEFT(subj_code, LEN(@subj_code)) = @subj_code) and
(LEN(subj_code) = LEN(@subj_code) + 1) and subj_code in (select cw_MedicalData.subj_code
from cw_MedicalData ,cw_DataTime where cw_MedicalData.tally_no=cw_DataTime.tally_no and cw_DataTime.tally_date>=@start_time and cw_DataTime.tally_date<=@end_time)
order by subj_code;
DECLARE cw_subject_view CURSOR FOR
select * from #result2
OPEN cw_subject_view;
fetch next from cw_subject_view into @code,@name,@amount
while(@@fetch_status=0)
begin
select @amount=sum(amount) from cw_MedicalData
where (LEFT(subj_code, LEN(@code)) = @code) and subj_code in (select cw_MedicalData.subj_code
from cw_MedicalData ,cw_DataTime where cw_MedicalData.tally_no=cw_DataTime.tally_no and cw_DataTime.tally_date>=@start_time and cw_DataTime.tally_date<=@end_time)
if(@amount is not null)
begin
update #result2 set amount =@amount where subj_code=@code
end
fetch next from cw_subject_view into @code,@name,@amount
end
close cw_subject_view
deallocate cw_subject_view
select * from #result2;
drop table #result2;
end
else
begin
if exists (select * from cw_MedicalData where subj_code=@subj_code)
begin
select a.subj_code,a.subj_name,b.amount,b.tabler,b.summary
from cw_subject a,cw_MedicalData b
where a.subj_code=b.subj_code and a.subj_code=@subj_code and a.subj_code in (select cw_MedicalData.subj_code
from cw_MedicalData ,cw_DataTime where cw_MedicalData.tally_no=cw_DataTime.tally_no and cw_DataTime.tally_date>=@start_time and cw_DataTime.tally_date<=@end_time)
end
else
begin
select subj_code,subj_name,0 as amount,null as tabler,null as summary
from cw_subject
where subj_code=@subj_code and subj_code in (select cw_MedicalData.subj_code
from cw_MedicalData ,cw_DataTime where cw_MedicalData.tally_no=cw_DataTime.tally_no and cw_DataTime.tally_date>=@start_time and cw_DataTime.tally_date<=@end_time)
end
end
end
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
