使用事务来处理!create proc deleteSame as begin begin Transaction declare @error1 int, @error2 int declare @indextable table(id int ,names varchar(20)) insert @indextable(id) select id from (select b.* from a inner join b on a.id =b.id) x begin delete from a where a.id in(select id from @indextable) set @error1 = @@error end begin delete from b where b.id in (select id from @indextable) set @error2 = @@error end if(@error1<>0 or @error2<>0) rollback else commit endexec deleteSame
delete A表,b表 --这是错误的,同时指定删两个表
放到一个存储过程中去执行.CREATE PROCEDURE myproc as begin delete from A where ... delete from B where ... end
CREATE PROCEDURE Del as delete from Test1 where A='11' delete from Test2 where A1='11' delete from Test3 where A3='11' goTest1,Test2,Test3为表名 A,A1,A3是表中的字段名执行的时候用 EXEC Del
as
begin
begin Transaction
declare @error1 int, @error2 int
declare @indextable table(id int ,names varchar(20))
insert @indextable(id) select id from (select b.* from a inner join b on a.id =b.id) x
begin
delete from a where a.id in(select id from @indextable)
set @error1 = @@error
end
begin
delete from b where b.id in (select id from @indextable)
set @error2 = @@error
end
if(@error1<>0 or @error2<>0)
rollback
else
commit
endexec deleteSame
as
begin
delete from A where ...
delete from B where ...
end
as
delete from Test1 where A='11'
delete from Test2 where A1='11'
delete from Test3 where A3='11'
goTest1,Test2,Test3为表名 A,A1,A3是表中的字段名执行的时候用 EXEC Del