删除用户或主管部门的存储过程.txt

ASP程序实现的科技期刊系统

/*
功能说明:用于删除用户的存储过程；当删除主管部门时，同时删除下属二级用户编码及负责项目
涉及数据对象:XT_TD_User,XT_TP_UserRole,XT_TD_UserModule,XT_TC_Project
制做人:刘晓颖
制做日期:2002-11-7
*/
CREATE  PROCEDURE pr_XT_UserDelete
@chrUserCode VARCHAR(6)  --用户编码
AS
BEGIN TRANSACTION UserDelete
DELETE XT_TC_User
WHERE 用户编码=@chrUserCode
DELETE XT_TP_UserRole
WHERE 用户编码=@chrUserCode
DELETE XT_TD_UserModule
WHERE 用户编码=@chrUserCode

DELETE XT_TD_ProjectCompany
WHERE 项目编码 IN (select 项目编码 from xt_td_Project where 项目负责人=@chrUserCode)
DELETE XT_TD_ProjectBasic
WHERE 项目编码 IN (select 项目编码 from xt_td_Project where 项目负责人=@chrUserCode)
DELETE XT_TD_ProjectPerson
WHERE 项目编码 IN (select 项目编码 from xt_td_Project where 项目负责人=@chrUserCode)
DELETE XT_TD_ProjectFee
WHERE 项目编码 IN (select 项目编码 from xt_td_Project where 项目负责人=@chrUserCode)
DELETE XT_TD_ProjectFeeNum
WHERE 项目编码 IN (select 项目编码 from xt_td_Project where 项目负责人=@chrUserCode)
DELETE XT_TD_ProjectSupplyNew1
WHERE 项目编码 IN (select 项目编码 from xt_td_Project where 项目负责人=@chrUserCode)
DELETE XT_TD_ProjectSupplyNew2
WHERE 项目编码 IN (select 项目编码 from xt_td_Project where 项目负责人=@chrUserCode)
DELETE XT_TD_ProjectSupplyNew3
WHERE 项目编码 IN (select 项目编码 from xt_td_Project where 项目负责人=@chrUserCode)
DELETE XT_TD_ProjectSupplyNew4
WHERE 项目编码 IN (select 项目编码 from xt_td_Project where 项目负责人=@chrUserCode)
DELETE XT_TD_ProjectSupplyIndustry
WHERE 项目编码 IN (select 项目编码 from xt_td_Project where 项目负责人=@chrUserCode)
DELETE XT_TD_ProjectAffix
WHERE 项目编码 IN (select 项目编码 from xt_td_Project where 项目负责人=@chrUserCode)

DELETE XT_TD_Project
WHERE 项目负责人=@chrUserCode

IF LEN(@chrUserCode)=3
  BEGIN
	DELETE XT_TC_User
	WHERE LEFT(用户编码,3)=@chrUserCode

	DELETE XT_TD_ProjectCompany
	WHERE 项目编码 IN (select 项目编码 from xt_td_Project where LEFT(项目负责人,3)=@chrUserCode)
	DELETE XT_TD_ProjectBasic
	WHERE 项目编码 IN (select 项目编码 from xt_td_Project where LEFT(项目负责人,3)=@chrUserCode)
	DELETE XT_TD_ProjectPerson
	WHERE 项目编码 IN (select 项目编码 from xt_td_Project where LEFT(项目负责人,3)=@chrUserCode)
	DELETE XT_TD_ProjectFee
	WHERE 项目编码 IN (select 项目编码 from xt_td_Project where LEFT(项目负责人,3)=@chrUserCode)
	DELETE XT_TD_ProjectFeeNum
	WHERE 项目编码 IN (select 项目编码 from xt_td_Project where LEFT(项目负责人,3)=@chrUserCode)
	DELETE XT_TD_ProjectSupplyNew1
	WHERE 项目编码 IN (select 项目编码 from xt_td_Project where LEFT(项目负责人,3)=@chrUserCode)
	DELETE XT_TD_ProjectSupplyNew2
	WHERE 项目编码 IN (select 项目编码 from xt_td_Project where LEFT(项目负责人,3)=@chrUserCode)
	DELETE XT_TD_ProjectSupplyNew3
	WHERE 项目编码 IN (select 项目编码 from xt_td_Project where LEFT(项目负责人,3)=@chrUserCode)
	DELETE XT_TD_ProjectSupplyNew4
	WHERE 项目编码 IN (select 项目编码 from xt_td_Project where LEFT(项目负责人,3)=@chrUserCode)
	DELETE XT_TD_ProjectSupplyIndustry
	WHERE 项目编码 IN (select 项目编码 from xt_td_Project where LEFT(项目负责人,3)=@chrUserCode)
	DELETE XT_TD_ProjectAffix
	WHERE 项目编码 IN (select 项目编码 from xt_td_Project where LEFT(项目负责人,3)=@chrUserCode)

	DELETE XT_TD_Project
	WHERE LEFT(项目负责人,3)=@chrUserCode
  END
COMMIT TRANSACTION UserDelete
IF @@ERROR=0 
   RETURN(0)
ELSE
   BEGIN
      ROLLBACK TRANSACTION UserDelete
      RETURN(-1)
   END
GO