一个oracle与 sql server 的问题

Question

create or replace procedure xpro_refreshOrgChart(empType number) is
begindelete xt_departmentrefresh;if empType != 0 then
insert into xt_departmentrefresh
  select substr(d.treeid, 0, length(d.treeid) - 2),
         substr(d.treeid, 0, length(d.treeid) - 2),
         d.treeid,
         d.treeid,
         d.departmentid,
         dd.empcount || '人' || '/' ||    d.departmentname  
    from xdepartment d,
         (select dd.treeid,sum(empCount) as empcount from xdepartment dd ,         
          (select d.treeid,count(e.employeeid) empCount
          from xdepartment d ,  (select employeeid,departmentid,type1 from xemployee where lastworkdate is null or lastworkdate > sysdate) e 
          where e.departmentid (+)= d.departmentid and e.type1 = empType
          group by d.treeid )ee where ee.treeid like dd.treeid||'%'
          group by dd.treeid) dd
          where d.treeid = dd.treeid;
else
insert into xt_departmentrefresh
  select substr(d.treeid, 0, length(d.treeid) - 2),
         substr(d.treeid, 0, length(d.treeid) - 2),
         d.treeid,
         d.treeid,
         d.departmentid,
         dd.empcount || '人' || '/' ||    d.departmentname  
    from xdepartment d,
         (select dd.treeid,sum(empCount) as empcount from xdepartment dd ,         
          (select d.treeid,count(e.employeeid) empCount
          from xdepartment d ,  (select employeeid,departmentid,type1 from xemployee where lastworkdate is null or lastworkdate > sysdate) e 
          where e.departmentid (+)= d.departmentid
          group by d.treeid )ee where ee.treeid like dd.treeid||'%'
          group by dd.treeid) dd
          where d.treeid = dd.treeid;
end if;end xpro_refreshOrgChart;上面这个储存过程在oracle中是正确，如把转成sql sverver 

Answer 1

Create  procedure xpro_refreshOrgChart 
@empType int
as
begin
  drop table  xt_departmentrefresh  if (@empType <> 0)
  begin
    insert into xt_departmentrefresh
    select substring(d.treeid, 0, len(d.treeid) - 2),
         substring(d.treeid, 0, len(d.treeid) - 2),
         d.treeid,
         d.treeid,
         d.departmentid,
         dd.empcount+ '人' + '/' +d.departmentname  
    from xdepartment d,
         (select dd.treeid,sum(empCount) as empcount from xdepartment dd ,         
          (select d.treeid,count(e.employeeid) empCount from xdepartment d 
          left join  (select employeeid,departmentid,type1 from xemployee where lastworkdate is null or lastworkdate > sysdate) e 
          on e.departmentid = d.departmentid and e.type1 = @empType
          group by d.treeid )ee where ee.treeid like dd.treeid+'%'
          group by dd.treeid) dd
          where d.treeid = dd.treeid
  end
  else
  begin
    insert into xt_departmentrefresh
    select substring(d.treeid, 0, len(d.treeid) - 2),
         substring(d.treeid, 0, len(d.treeid) - 2),
         d.treeid,
         d.treeid,
         d.departmentid,
         dd.empcount + '人' + '/' +d.departmentname  
    from xdepartment d,
         (select dd.treeid,sum(empCount) as empcount from xdepartment dd ,         
          (select d.treeid,count(e.employeeid) empCount
          from xdepartment d 
          left join (select employeeid,departmentid,type1 from xemployee where lastworkdate is null or lastworkdate > sysdate) e 
          on e.departmentid = d.departmentid
          group by d.treeid )ee where ee.treeid like dd.treeid+'%'
          group by dd.treeid) dd
          where d.treeid = dd.treeid
  end
end