sql server存储过程转化为DB2存储过程
小弟有个sql server的存储过程,不会转化为DB2的,希望大家给我弄下,谢谢!急用.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[getBanJianTongJibaobiao1]
@p_district_no int,
@p_depa_no varchar(10),
@p_subdp_no varchar(20),
@StartDate datetime,
@OKDay datetime
AS
Declare @Name varchar(50)
Declare @shoujian bigint
Declare @bianjian bigint
Declare @type_id bigint
Declare @type_context varchar(50)
CREATE TABLE #t ( casecontext varchar(50),
shoujian bigint,
banjian bigint
)
Declare My_CURSOR CURSOR
FOR
select type_id,ltrim(rtrim(type_context)) as type_context from t_casetype
open My_CURSOR
FETCH NEXT FROM My_CURSOR INTO @type_id,@type_context
while (@@FETCH_STATUS = 0)
BEGIN
set @shoujian=0
set @bianjian=0
if @p_district_no=-1
begin
select @shoujian=isnull(sum(case_number),0) from t_case where case_typeid=@type_id and datediff(day,case_startdate ,@StartDate) <= 0 and datediff(day,case_startdate ,@OKDay) >= 0 and case_statusId <>5
select @bianjian=isnull(sum(case_number),0) from t_case where case_typeid=@type_id and datediff(day,case_okday ,@StartDate) <= 0 and datediff(day,case_okday ,@OKDay) >= 0 and case_statusId in (2,4)
end
else
begin
if @p_depa_no='all'
begin
--如果是县中心，不统计代办件的受理件数
if(@p_district_no=1)
select @shoujian=isnull(sum(a.case_number),0) from t_case a left join t_department b on a.case_Depa_No=b.Depa_No where b.Depa_Dist_No=@p_district_no and a.case_typeid=@type_id and datediff(day,a.case_startdate ,@StartDate) <= 0 and datediff(day,a.case_startdate ,@OKDay) >= 0 and a.case_statusId <>5 and a.case_typeid<>8
else
select @shoujian=isnull(sum(a.case_number),0) from t_case a left join t_department b on a.case_Depa_No=b.Depa_No where b.Depa_Dist_No=@p_district_no and a.case_typeid=@type_id and datediff(day,a.case_startdate ,@StartDate) <= 0 and datediff(day,a.case_startdate ,@OKDay) >= 0 and a.case_statusId <>5
select @bianjian=isnull(sum(a.case_number),0) from t_case a left join t_users b on a.case_bjuserid=b.user_id
where b.user_Dist_No=@p_district_no and a.case_typeid=@type_id and datediff(day,a.case_okday ,@StartDate) <= 0 and datediff(day,a.case_okday ,@OKDay) >= 0 and a.case_statusId in (2,4)
end
else
begin
if @p_subdp_no='all'
begin
--如果是县中心，不统计代办件的受理件数
if(@p_district_no=1)
select @shoujian=isnull(sum(case_number),0) from t_case where case_typeid=@type_id and datediff(day,case_startdate ,@StartDate) <= 0 and datediff(day,case_startdate ,@OKDay) >= 0 and case_statusId <>5 and case_Depa_No = @p_depa_no and case_typeid<>8
else
select @shoujian=isnull(sum(case_number),0) from t_case where case_typeid=@type_id and datediff(day,case_startdate ,@StartDate) <= 0 and datediff(day,case_startdate ,@OKDay) >= 0 and case_statusId <>5 and case_Depa_No = @p_depa_no
select @bianjian=isnull(sum(case_number),0) from t_case a left join t_users b on a.case_bjuserid=b.user_id where case_typeid=@type_id and datediff(day,case_okday ,@StartDate) <= 0 and datediff(day,case_okday ,@OKDay) >= 0 and case_statusId in (2,4) and b.user_depa_no = @p_depa_no
select @bianjian=isnull(sum(case_number),0) from t_case a left join t_service c on a.case_serv_NO=c.serv_no where case_typeid=@type_id and datediff(day,case_okday ,@StartDate) <= 0 and datediff(day,case_okday ,@OKDay) >= 0 and case_statusId in (2,4) and c.serv_depa_no = @p_depa_no
end
else
begin
--如果是县中心，不统计代办件的受理件数
if(@p_district_no=1)
select @shoujian=isnull(sum(case_number),0) from t_case a left join t_service c on a.case_serv_NO=c.serv_no where case_typeid=@type_id and datediff(day,case_startdate ,@StartDate) <= 0 and datediff(day,case_startdate ,@OKDay) >= 0 and case_statusId <>5 and case_Depa_No = @p_depa_no and (c.serv_subDP=@p_subdp_no or c.serv_subdp='0') and case_typeid<>8
else
select @shoujian=isnull(sum(case_number),0) from t_case a left join t_service c on a.case_serv_NO=c.serv_no where case_typeid=@type_id and datediff(day,case_startdate ,@StartDate) <= 0 and datediff(day,case_startdate ,@OKDay) >= 0 and case_statusId <>5 and c.serv_depa_no = @p_depa_no and (c.serv_subDP=@p_subdp_no or c.serv_subdp='0')
--是窗口工作人员
select @bianjian=isnull(sum(case_number),0) from t_case a left join t_service c on a.case_serv_NO=c.serv_no where case_typeid=@type_id and datediff(day,case_okday ,@StartDate) <= 0 and datediff(day,case_okday ,@OKDay) >= 0 and case_statusId in (2,4) and c.serv_depa_no = @p_depa_no and (c.serv_subDP=@p_subdp_no or c.serv_subdp='0')
end
end
end
Insert Into #t (casecontext,shoujian,banjian) values(@type_context,@shoujian,@bianjian)
FETCH NEXT FROM My_CURSOR INTO @type_id,@type_context
END
CLOSE My_CURSOR
DEALLOCATE My_CURSOR
select * from #t
drop table #t |