SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GOALTER PROCEDURE dbo.INSERTFORMCHECK
@Form_ID varchar(20),@Form_type int,@Form_EmpID varchar(20),@return varchar(10) output
AS
begin transaction
select @return = 'Success'
if exists(select * from FORMCHECKING where Form_ID = @Form_ID )
begin
select @return = 'Fail'
rollback transaction
return
end
declare @Formcheck_level varchar(20),@Dept_ID int,@Joblevel_ID int,
@Employee varchar(20)declare Fromsetup_Cursor cursor for
select Formcheck_level,Dept_ID,Joblevel_ID
from FORMCHECKSETUP
where Formtype_ID = @Form_type
OPEN Fromsetup_Cursorfetch next from Fromsetup_Cursor into @Formcheck_level,@Dept_ID,@Joblevel_IDWHILE @@FETCH_STATUS = 0
BEGIN
IF @Dept_ID=0
begin
declare @tmpDept_ID int --申明临时部门变量
set @tmpDept_ID=(select top 1 dept_id from EMPLOYEERELATION
where employee_id=@Form_EmpID order by ifprior desc )
set @Employee=(select top 1 Employee_ID from EMPLOYEERELATION
where Dept_id=@tmpDept_ID
and Joblevel_id=@Joblevel_ID order by Checkprior desc
)
insert into FORMCHECKING (Form_ID,Formtype_ID,Formcheck_Level,Employee_ID)
values (@Form_ID,@Form_type,@Formcheck_level,@Employee)
end
else
begin
set @Employee=(select top 1 Employee_ID from EMPLOYEERELATION
where Dept_id=@Dept_ID and Joblevel_id=@Joblevel_ID order by Ifprior desc)
insert into FORMCHECKING (Form_ID,Formtype_ID,Formcheck_Level,Employee_ID)
values (@Form_ID,@Form_type,@Formcheck_level,@Employee)
end
fetch next from Fromsetup_Cursor into @Formcheck_level,@Dept_ID,@Joblevel_ID
END if @@error = 0
commit transaction
else begin
select @return = 'Fail'
rollback transaction
end CLOSE Fromsetup_Cursor
DEALLOCATE Fromsetup_Cursor
return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GO
SET ANSI_NULLS OFF
GOALTER PROCEDURE dbo.INSERTFORMCHECK
@Form_ID varchar(20),@Form_type int,@Form_EmpID varchar(20),@return varchar(10) output
AS
begin transaction
select @return = 'Success'
if exists(select * from FORMCHECKING where Form_ID = @Form_ID )
begin
select @return = 'Fail'
rollback transaction
return
end
declare @Formcheck_level varchar(20),@Dept_ID int,@Joblevel_ID int,
@Employee varchar(20)declare Fromsetup_Cursor cursor for
select Formcheck_level,Dept_ID,Joblevel_ID
from FORMCHECKSETUP
where Formtype_ID = @Form_type
OPEN Fromsetup_Cursorfetch next from Fromsetup_Cursor into @Formcheck_level,@Dept_ID,@Joblevel_IDWHILE @@FETCH_STATUS = 0
BEGIN
IF @Dept_ID=0
begin
declare @tmpDept_ID int --申明临时部门变量
set @tmpDept_ID=(select top 1 dept_id from EMPLOYEERELATION
where employee_id=@Form_EmpID order by ifprior desc )
set @Employee=(select top 1 Employee_ID from EMPLOYEERELATION
where Dept_id=@tmpDept_ID
and Joblevel_id=@Joblevel_ID order by Checkprior desc
)
insert into FORMCHECKING (Form_ID,Formtype_ID,Formcheck_Level,Employee_ID)
values (@Form_ID,@Form_type,@Formcheck_level,@Employee)
end
else
begin
set @Employee=(select top 1 Employee_ID from EMPLOYEERELATION
where Dept_id=@Dept_ID and Joblevel_id=@Joblevel_ID order by Ifprior desc)
insert into FORMCHECKING (Form_ID,Formtype_ID,Formcheck_Level,Employee_ID)
values (@Form_ID,@Form_type,@Formcheck_level,@Employee)
end
fetch next from Fromsetup_Cursor into @Formcheck_level,@Dept_ID,@Joblevel_ID
END if @@error = 0
commit transaction
else begin
select @return = 'Fail'
rollback transaction
end CLOSE Fromsetup_Cursor
DEALLOCATE Fromsetup_Cursor
return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
try
delete from table1 where ....
insert table2(..) values (...)
update table3 set ....
CommitTrans;
except
RollbackTrans;
end;
delete from table1 where ....
insert table2(..) values (...)
update table3 set ....if @@error=0
begin
CommitTrans;
endelse
RollbackTrans;