sportdog

导航

 

业务: HR的入职人员及离职人员信息同步到OA系统

思路: 从HR系统取到数据,如为入职则插入一条,入为离职这设置该员工为离职状态并清空Ad字段。

调试: 用vs2008链接到该数据库服务器,找到该存储过程,再右键(单步执行存储过程)就可以调试了。

ALTER PROCEDURE dbo.Proc_HrToOa
AS
declare @staffno varchar(20)
declare @staffname varchar(20)
declare @dept varchar(20)
declare @staffstatus int
declare yb cursor for
select staff_no,staff_name,staff_dept,staff_status from dbo.Hr_Tab_Test_User
open yb
fetch next from yb into @staffno,@staffname,@dept,@staffstatus
while @@fetch_status=0
begin
 if @staffstatus=1
  begin
   if not exists(select * from hr_tab_user where staffNumber=@staffno)
    begin
    insert into hr_tab_user(staffnumber,staffname,hrdeptcode,status,adaccount)
    values(@staffno,@staffname,@dept,1,@staffno+'@greatwall.com.cn')
   end
  end
 else
  begin
   if exists(select * from hr_tab_user where staffNumber=@staffno
   begin
    update hr_tab_user set status=0,adaccount='' where staffNumber=@staffno
   end
  end
  
 fetch next from yb into @staffno,@staffname,@dept,@staffstatus
end
close yb
deallocate yb

 

posted on 2014-02-25 15:31  sportdog  阅读(211)  评论(0编辑  收藏  举报