proc_addaccount

物业管理和办公自动化系统

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 存储过程(新员工登记，分配OA账号，并自动在T_OnlineStatus中增加一条记录)
-- 返回值   0: 成功；
--        -1: 不成功
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE proc_AddAccount(@pName varchar(50), @pAccountId varchar(20), @pCoId int, @pIsDummy int, @pDismissed int)
As
Set NOCOUNT ON
--define variables
declare @emp_serial	int		-- 新增员工的序列号
declare @rtn_code	int		-- 存储过程执行的结果

select @emp_serial = 0
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 以下 T_Employee、T_Account、T_OnlineStatus、T_Email 的操作作为一个事务提交
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Begin Tran
----------------------------------------------------------------------------------------
-- 向员工表 T_Employee 中插入一条记录，并得到刚刚插入的记录的序列号，也就是员工标识
----------------------------------------------------------------------------------------
select @rtn_code = count(*) from t_account where account_id = @pAccountId
if @rtn_code > 0
     begin
          goto BeforeReturn
     end

insert into t_employee(account_id, name, co_email, co_id, isdummy, dismissed)
     values(@pAccountId, @pName, @pAccountId, @pCoId, @pIsDummy, @pDismissed)

select @emp_serial = @@IDENTITY

----------------------------------------------------------------------------------------
-- 向账号表 T_Account 中插入一条记录
----------------------------------------------------------------------------------------
insert into T_Account(Account_id, Password, Status)
	values(@pAccountId, @pAccountId, 0)
if @@ROWCOUNT = 0 
     begin
	rollback tran
	goto BeforeReturn
     end

----------------------------------------------------------------------------------------
-- 向在线状态表 T_OnlineStatus 中插入一条记录
----------------------------------------------------------------------------------------
insert into T_OnlineStatus(Account_id, Login_Status, Online_Status)
	values(@pAccountId, 0, 0)
if @@ROWCOUNT = 0
    begin
	rollback tran
	goto BeforeReturn
    end

----------------------------------------------------------------------------------------
-- 向T_Email 表中插入一条记录
----------------------------------------------------------------------------------------
insert into T_Email(userid, password, fullname, userdir, mailaddr, flags)
	select co_email, co_email, name, 'c:\imail\users\'+co_email, co_email+'@infortower.com', 134 from T_Employee
	where serial = @emp_serial
if @@ROWCOUNT = 0
     begin
          rollback tran
	  goto BeforeReturn
     end

Commit Tran
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 以上 T_Employee、T_Account、T_OnlineStatus、T_Email 的操作作为一个事务提交
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BeforeReturn:
	select @emp_serial as 'rtn_code'
GO