If( @ThirdlyUserID <>0)
Begin
Update Cheeda_ManagerFile
Set MFileAuditing=6
Where MFileID=@MFileID
End If(@SeconddlyUserID <>0 )
Begin
Update Cheeda_ManagerFile
Set MFileAuditing=5
Where MFileID=@MFileID
End If( @ThirdlyUserID <>0)
Begin
Update Cheeda_ManagerFile
Set MFileAuditing=4
Where MFileID=@MFileID
End
Begin
Update Cheeda_ManagerFile
Set MFileAuditing=6
Where MFileID=@MFileID
End If(@SeconddlyUserID <>0 )
Begin
Update Cheeda_ManagerFile
Set MFileAuditing=5
Where MFileID=@MFileID
End If( @ThirdlyUserID <>0)
Begin
Update Cheeda_ManagerFile
Set MFileAuditing=4
Where MFileID=@MFileID
End
全部代码都已经修改好了!CREATE PROCEDURE ch_MFilesToAudithing
(
@MFileID char(10),
@UserID int,--办公室审核人ID
@FirstUserID int,--一级审核人员(分管领导)ID
@SecondlyUserID int,--二级审核人员(管理者副代表)ID
@ThirdlyUserID int--三级审核人员(管理者代表)ID
)
AS
SET NOCOUNT ON
BEGIN TRAN/*提交办公室审核人员信息*/
Insert into Cheeda_ManagerFileAuditing(MFileID,MFileStatu,UserID)
Values (@MFileID,3,@FirstUserID)
IF @@ERROR<>0 GOTO errHandle/*如果一级审核人员(分管领导)ID不为0,写入一级审核人员信息*/
IF(@FirstUserID <>0)
Begin
Insert into Cheeda_ManagerFileAuditing(MFileID,MFileStatu,UserID)
Values (@MFileID,4,@FirstUserID)
IF @@ERROR<>0 GOTO errHandle
End /*如果二级审核人员(管理者副代表)ID不为0,写入二级审核人员信息*/
IF(@SecondlyUserID <>0)
Begin
Insert into Cheeda_ManagerFileAuditing(MFileID,MFileStatu,UserID)
Values (@MFileID,5,@SecondlyUserID)
IF @@ERROR<>0 GOTO errHandle
End /*如果三级审核人员(管理者代表)ID不为0,写入三级审核人员信息*/
IF(@ThirdlyUserID <>0)
Begin
Insert into Cheeda_ManagerFileAuditing(MFileID,MFileStatu,UserID)
Values (@MFileID,6,@ThirdlyUserID)
IF @@ERROR<>0 GOTO errHandle
End /*
更新体系文件流转状态为审核状态(4)
这里就是问题了
如果@FirstUserID、@SeconddlyUserID、@ThirdlyUserID都不为0,就将MFileAuditing设置为4
如果@FirstUserID、@SeconddlyUserID都不为0,就将MFileAuditing设置为4
如果@FirstUserID、@ThirdlyUserID都不为0,就将MFileAuditing设置为4
如果@SeconddlyUserID、@ThirdlyUserID都不为0,就将MFileAuditing设置为5
....
如果ThirdlyUserID都不为0,就将MFileAuditing设置为6
....
也就是根据@FirstUserID、@SeconddlyUserID、@ThirdlyUserID来确定一个值(4,5,6),而且这个值必须是最小的
@FirstUserID对应设置的值为4
@SeconddlyUserID对应设置的值为5
@ThirdlyUserID对应设置的值为6
不知道我说清楚没有,请各位高手帮帮忙,谢谢!
*/ Update Cheeda_ManagerFile
Set MFileAuditing=case when @FirstUserID <>0 and (@SecondlyUserID <>0 or @ThirdlyUserID <>0) then 4
when @SecondlyUserID <>0 and @ThirdlyUserID <>0 then 5
when @ThirdlyUserID <>0 then 6
else null end
Where MFileID=@MFileID
IF @@ERROR<>0 GOTO errHandleCOMMIT TRAN
RETURNerrHandle:
ROLLBACK TRAN
RETURN
GO