if exists (select * from sysobjects where id = object_id('dbo.spw_ModifyWorkerMan') and sysstat & 0xf = 4)
drop procedure dbo.spw_ModifyWorkerMan
go
create procedure spw_ModifyWorkerMan
@sqltable varchar(20)as begin transaction modworkerman
if(@@trancount>1)
save transaction modworkerman
declare
@sno char(4),
@sname varchar(10),
@sbusiness varchar(10),
@sflag char(1),
@str varchar(255)
select @str='select * into ##WORKERMAN from ' +@sqltable+' where 1=1'
exec (@str)
if (@@rowcount<0)
goto L_ROLLBACK
declare cur_workerman cursor
for select WM_NO,WM_NAME,WM_BUSINESS,WM_FLAG
from ##WORKERMAN open cur_workerman;
fetch cur_workerman into @sno,@sname,@sbusiness,@sflag
while (@@fetch_status=0)
begin
if (exists(select * from WORKERMAN where WM_NO=@sno))
begin
update WORKERMAN set
WM_BUSINESS=@sbusiness,
WM_NAME=@sname,
WM_FLAG=@sflag
where WM_NO=@sno
if (@@rowcount<>1)
goto L_ROLLBACK
end
else
begin
insert into WORKERMAN values(
@sno,
@sname,
@sbusiness,
@sflag
)
if (@@rowcount<>1)
goto L_ROLLBACK
end
fetch cur_workerman into @sno,@sname,@sbusiness,@sflag ;
end
CLOSE cur_workerman
DEALLOCATE cur_workerman;
L_COMMIT:
drop table ##WORKERMAN
commit transaction
return 0
L_ROLLBACK:
rollback transaction modworkerman
if (@@trancount>1)
commit transaction
return -1
go
drop procedure dbo.spw_ModifyWorkerMan
go
create procedure spw_ModifyWorkerMan
@sqltable varchar(20)as begin transaction modworkerman
if(@@trancount>1)
save transaction modworkerman
declare
@sno char(4),
@sname varchar(10),
@sbusiness varchar(10),
@sflag char(1),
@str varchar(255)
select @str='select * into ##WORKERMAN from ' +@sqltable+' where 1=1'
exec (@str)
if (@@rowcount<0)
goto L_ROLLBACK
declare cur_workerman cursor
for select WM_NO,WM_NAME,WM_BUSINESS,WM_FLAG
from ##WORKERMAN open cur_workerman;
fetch cur_workerman into @sno,@sname,@sbusiness,@sflag
while (@@fetch_status=0)
begin
if (exists(select * from WORKERMAN where WM_NO=@sno))
begin
update WORKERMAN set
WM_BUSINESS=@sbusiness,
WM_NAME=@sname,
WM_FLAG=@sflag
where WM_NO=@sno
if (@@rowcount<>1)
goto L_ROLLBACK
end
else
begin
insert into WORKERMAN values(
@sno,
@sname,
@sbusiness,
@sflag
)
if (@@rowcount<>1)
goto L_ROLLBACK
end
fetch cur_workerman into @sno,@sname,@sbusiness,@sflag ;
end
CLOSE cur_workerman
DEALLOCATE cur_workerman;
L_COMMIT:
drop table ##WORKERMAN
commit transaction
return 0
L_ROLLBACK:
rollback transaction modworkerman
if (@@trancount>1)
commit transaction
return -1
go
delete from tablename where bh='001'
if @@error>0
rollback tran
else
commit tran
goto L_ROLLBACK是甚麼意思啊/謝謝
呆,这种问题不回答了,自己看帮助
BEGIN TRANSACTION
CONNECT
DISCONNECT
COMMIT
ROLLBACK
标准事务语句:
BEGIN TRANSACTION
CONNECT
DISCONNECT
COMMIT
ROLLBACK
d事务支持的好的可能是sql server
他本来就是t_sql要注意oracle丫!
save transaction trans_name
rollback transaction trans_name
commit transaction trans_name