存储过程

create procedure njutec_wdtjpc01

( 
    @rExeStartDate varchar(8),    --统计开始时间 eg:20140101
    @rExeEndDate varchar(8),     --统计结束时间 eg:20140701
    @rType int                              --统计对象 3:网点排查 2:网管员排查 1：市行排查  0：省行排查
) 
AS
DECLARE
    @sts varchar(20),     --条件参数
    @pcsts varchar(20), --插入结果表 数据值
    @jzsj varchar(20),     --截止时间 月份
    @maxId int
declare @error int
declare @temp0 varchar(50)  --临时变量，用来保存游标值
declare @temp1 varchar(50)  --临时变量，用来保存游标值
declare @temp2 int                --临时变量，用来保存游标值
declare @temp3 int                --临时变量，用来保存游标值
declare @temp4 int                --临时变量，用来保存游标值
declare @temp5 int                --临时变量，用来保存游标值
declare @temp6 int                --临时变量，用来保存游标值
declare @temp7 int                --临时变量，用来保存游标值
BEGIN
    set @jzsj = substring(@rExeEndDate,1,6)
if  @rType = 3  --网点排查
    begin
        set @sts= '1' --参数
        set @pcsts='3'
    end
else if @rType = 2  --网管部排查
    begin
        set @sts= '0'
        set @pcsts='2'
    end
else if @rType = 1  --市行排查
    begin
        set @sts='99'
        set @pcsts='1'
    end
else if @rType = 0  --省行排查
    begin
        set @sts= '00'
        set @pcsts='0'
    end
set @error=0
--清理重复数据
delete from STD_PC_WDPCTJ01 WHERE tjtype = @pcsts and jzsj = @jzsj

BEGIN TRAN --声明事务
--定义游标
declare first_cursor CURSOR FOR select a.ORGID,a.NAME,isnull(b.totalPsn,0) totalPsn,
isnull(c.leaders,0) leaders,isnull(b.totalPsn-c.leaders,0) as others,
isnull(d.totalimp,0) totalimp,isnull(pcTotalPsns,0) pcTotalPsns,
isnull(f.pcTotalTimes,0) pcTotalTimes
from
(select ORGID,NAME from PLT_ORG_DEPT where DEPTTYPE='1' and ORGID like 'A%' and NETSTYP = '1' and STATE='0' and ISDEPT='0') a
left join (select ORGID,count(*) as totalPsn from EMP_ORG_REL where STS = '0' and PREF = '1' group by ORGID) b --totalPsn网点总人数
on a.ORGID=b.ORGID
left join
(select count(*) as leaders,e.ORGID from
(select c.STFID ,d.ORGID from (select STFID from STD_SJ_POS where POSITION in('01','02','03','04') and ISEFFECT = '0') c left join (SELECT EMPCOD,ORGID from EMP_ORG_REL where STS = '0' and PREF = '1' ) d on c.STFID = d.EMPCOD) e

group by e.ORGID ) c --leaders 领导班子
on a.ORGID = c.ORGID
left join
(select count(*) as totalimp, j. ORGID from(
select h.STFID,i.ORGID from (
(select g.STFID from STD_SJ_IMP g where g.ISIMP = '1' and ( g.IMPBGNTIM >= @rExeStartDate OR g.IMPENDTIM <= @rExeEndDate)) h
left join EMP_ORG_REL i on h.STFID = i.EMPCOD) ) j group by j.ORGID ) d on a.ORGID = d.ORGID
left join
(select count(*) as pcTotalPsns,m.ORGID from
(SELECT PCJLBPCR  FROM dbo.STD_SJ_PCJL where PCJLTYPE = @sts and PCJLPCRQ >= @rExeStartDate and PCJLPCRQ <= @rExeEndDate
group by PCJLBPCR) l
left join EMP_ORG_REL m on l.PCJLBPCR = convert(varchar,m.EMPID) group by m.ORGID) e  --排查人数
on a.ORGID = e.ORGID
left join
(select count(aaa.pcTotalTimes) pcTotalTimes,aaa.PCJLBPCRORG as ORGID from
(SELECT count(*) pcTotalTimes,PCJLBPCRORG  FROM dbo.STD_SJ_PCJL where PCJLPCRQ >= @rExeStartDate and PCJLPCRQ <= @rExeEndDate and PCJLTYPE = @sts group by  PCJLPCRQ,PCJLPCFS,PCJLPCR,PCJLBPCRORG) aaa group by aaa.PCJLBPCRORG) f --排查次数
on a.ORGID = f.ORGID

open first_cursor
FETCH NEXT FROM first_cursor INTO @temp0,@temp1,@temp2,@temp3,@temp4,@temp5,@temp6,@temp7
WHILE @@FETCH_STATUS = 0

--返回被 FETCH  语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。
begin
--开始循环游标变量
select @maxId=isnull(max(numid),0)+1 from STD_PC_WDPCTJ01

insert into STD_PC_WDPCTJ01(numid,orgid,name,totalpsn,leaders,others,totalimp,pctotalpsns,pctotaltimes,jzsj,tjtype) values (@maxId,
          @temp0,@temp1,@temp2,@temp3,@temp4,@temp5,@temp6,@temp7,@jzsj,@pcsts)
    set @error=@error+@@error
    FETCH NEXT FROM first_cursor INTO @temp0,@temp1,@temp2,@temp3,@temp4,@temp5,@temp6,@temp7
  end
if @error=0 --没有错误 统一提交事务
begin
  commit tran --提交                       
end
else
begin
  rollback tran --回滚
end
CLOSE first_cursor --关闭游标
DEALLOCATE first_cursor --释放游标
END