sql存储过程案例

alter procedure p_siey_proj_human_inout
@starttime datetime, 
@endtime datetime, 
@name varchar(100),
 @projectname  varchar(100)
as

create table #tmp
(
   humanid varchar(36),
   --humanname varchar(100),
   proj_guid varchar(36),
   atype int,
   posiname varchar(100),
   indate datetime,
   outdate datetime,
   ProjectLong int
)

insert into #tmp(atype,humanid,proj_guid,posiname,indate,outdate)
select 1,t1.HumanId,t1.EpsProjId,t1.PosiName,t1.ApprDate,t2.Actual_End_Date from(select x.Status,x.projectcode,x.EpsProjId,x.ApprDate,y.PosiName,z.HumanId,z.HumanName from PS_MK_ProjTeamEstablish x 
join PS_MK_ProjTeamEstablish_Posi y on x.Id=y.MasterId
join PS_MK_ProjTeamEstablish_Human z on x.Id=y.MasterId where x.Status='50') t1 join SY_Technologyclosure t2 on t1.EpsProjId=t2.EpsProjId

--select x.EpsProjName,x.ApprDate,y.PosiName,z.HumanId,z.HumanName from PS_MK_ProjTeamEstablish x 
--join PS_MK_ProjTeamEstablish_Posi y on x.Id=y.MasterId
--join PS_MK_ProjTeamEstablish_Human z on x.Id=y.MasterId where x.Status='50'

--select * from PS_CL_ProjectCloseApply

--select * from PB_PersonnelAllocation r join PB_PersonnelAllocationList d on r.Id=d.PersonnelAllocationId

---人员调拨
insert into #tmp(atype,humanid,indate,proj_guid)
select 2, d.HumId, d.TransferDate as indate, d.ToProjectId as proj_guid from PB_PersonnelAllocation r 
join PB_PersonnelAllocationList d 
on r.Id=d.PersonnelAllocationId
join PB_DefaultField e on r.id=e.DefaultFieldId
where r.AllocationType=0 or r.AllocationType = 20


--insert into #tmp(atype,humanid,outdate,proj_guid)
--select 2,d.HumId,d.TransferDate as outdate, d.ToProjectId as proj_guid from PB_PersonnelAllocation r 
--join PB_PersonnelAllocationList d 
--on r.Id=d.PersonnelAllocationId
--join PB_DefaultField e on r.id=e.DefaultFieldId
--where r.AllocationType=35

update #tmp set outdate = (select d.TransferDate as outdate from PB_PersonnelAllocation r 
join PB_PersonnelAllocationList d 
on r.Id=d.PersonnelAllocationId
join PB_DefaultField e on r.id=e.DefaultFieldId
where r.AllocationType=35 and #tmp.humanid=d.HumId and #tmp.atype=2 ) where #tmp.atype=2 

update #tmp set ProjectLong = DATEDIFF(DD,indate,outdate) where outdate is not null and indate is not null

select x1.*, x2.name as humanname,x2.Code as humancode, x3.project_name  
from #tmp x1
left outer join PB_Human x2 on x1.humanid=x2.id
left outer join pln_project x3 on x1.proj_guid= x3.project_guid where x2.Name like @name and x3.project_name like @projectname and  x1.indate BETWEEN CONVERT(datetime,@starttime,120) AND CONVERT(datetime,@endtime,120)

drop table #tmp

go

--exec aa '123';