============================================================*/ AS Declare @TID char(20) /*select top @TID= [plan] from (select m.* , m.plannum - isnull((select sum(Able_Amount) from Main_Out n where n.[Able_Plan] = m.[Plan]),0) val from main_plan m where m.pact_date<= @operate_Date ) t where val <= 0*/ select @TID=[plan] from (select m.* , m.plannum - isnull((select sum(Able_Amount) from Main_Out n where n.[Able_Plan] = m.[Plan]),0) val from main_plan m where m.pact_date<=@Operate_Date ) t where val <= 0 IF @@Rowcount < 1 SELECT @TID = '' Else Begin if exists(Select * From shouliao Where [plan]=@TID) Begin Delete From shouliao Where [plan]=@TID select convert(char,@Operate_Date)+'之前的收料单删除成功,共'+convert(char,@@Rowcount) end if exists(Select * From plant_lingWhere [plan]=@TID) Begin Delete From plant_ling Where [plan]=@TID select convert(char,@Operate_Date)+'之前的领料单删除成功,共'+convert(char,@@Rowcount) end Delete From main_product Where [plan]=@TID and txt<=0 select convert(char,@Operate_Date)+'之前的主仓库记录删除成功,共'+convert(char,@@Rowcount) Delete From plant_return Where [plan]=@TID select convert(char,@Operate_Date)+'之前的还料单删除成功,共'+convert(char,@@Rowcount) Delete From plant_recede where [able_plan]=@TID select convert(char,@Operate_Date)+'之前的退料单(退车间)删除成功,共'+convert(char,@@Rowcount) Delete from plant_rotate where able_plan=@TID select convert(char,@Operate_Date)+'之前的转货单删除成功,共'+convert(char,@@Rowcount) Delete From plant_product Where [plan]=@TID and txt<=0 select convert(char,@Operate_Date)+'之前的车间记录删除成功,共'+convert(char,@@Rowcount) Delete From Main_Out Where [Able_Plan]=@TID select convert(char,@Operate_Date)+'之前的出库单删除成功,共'+convert(char,@@Rowcount) Delete From Main_Fade Where [Able_Plan]=@TID select convert(char,@Operate_Date)+'之前的退料单(退客户)删除成功,共'+convert(char,@@Rowcount) */ End GO
Create proc test as
begin create #T (Tid int identity(1,1), A varchar, B varchar, c varchar) select A,B,C from A where 条件 insert #T declare @RowNow int ,@RowCount int set @RowNow = 1 select @RowCount = count(1) fom #t while(@RowNow<=@RowCount) begin delete from B where 同A条件 delete from C where 同A条件 set @RowNow = @RowNow+1 end end
INNER JOIN (SELECT A,B,C FROM TA WHERE 条件) A
ON A.A=B.A AND AND A.B=B.B AND A.C=B.C
C表跟上面一样
CREATE PROCEDURE sp_DeleteAllOverPlan
@Operate_Date datetime
/*============================================================
功能: 删除指定时间段里的所有的结束的计划及相关数据
参数:
@Operate_Date datetime : 计划单填写日期
============================================================*/
AS
Declare @TID char(20)
/*select top @TID= [plan] from (select m.* , m.plannum - isnull((select sum(Able_Amount) from Main_Out n where n.[Able_Plan] = m.[Plan]),0) val from main_plan m where m.pact_date<= @operate_Date ) t where val <= 0*/
select @TID=[plan] from (select m.* , m.plannum - isnull((select sum(Able_Amount) from Main_Out n where n.[Able_Plan] = m.[Plan]),0) val from main_plan m where m.pact_date<=@Operate_Date ) t where val <= 0
IF @@Rowcount < 1
SELECT @TID = ''
Else
Begin
if exists(Select * From shouliao Where [plan]=@TID)
Begin
Delete From shouliao Where [plan]=@TID
select convert(char,@Operate_Date)+'之前的收料单删除成功,共'+convert(char,@@Rowcount)
end
if exists(Select * From plant_lingWhere [plan]=@TID)
Begin
Delete From plant_ling Where [plan]=@TID
select convert(char,@Operate_Date)+'之前的领料单删除成功,共'+convert(char,@@Rowcount)
end
Delete From main_product Where [plan]=@TID and txt<=0
select convert(char,@Operate_Date)+'之前的主仓库记录删除成功,共'+convert(char,@@Rowcount)
Delete From plant_return Where [plan]=@TID
select convert(char,@Operate_Date)+'之前的还料单删除成功,共'+convert(char,@@Rowcount)
Delete From plant_recede where [able_plan]=@TID
select convert(char,@Operate_Date)+'之前的退料单(退车间)删除成功,共'+convert(char,@@Rowcount)
Delete from plant_rotate where able_plan=@TID
select convert(char,@Operate_Date)+'之前的转货单删除成功,共'+convert(char,@@Rowcount)
Delete From plant_product Where [plan]=@TID and txt<=0
select convert(char,@Operate_Date)+'之前的车间记录删除成功,共'+convert(char,@@Rowcount)
Delete From Main_Out Where [Able_Plan]=@TID
select convert(char,@Operate_Date)+'之前的出库单删除成功,共'+convert(char,@@Rowcount)
Delete From Main_Fade Where [Able_Plan]=@TID
select convert(char,@Operate_Date)+'之前的退料单(退客户)删除成功,共'+convert(char,@@Rowcount)
*/
End
GO
Create proc test
as
begin
create #T
(Tid int identity(1,1),
A varchar,
B varchar,
c varchar)
select A,B,C from A where 条件
insert #T declare @RowNow int ,@RowCount int
set @RowNow = 1
select @RowCount = count(1) fom #t
while(@RowNow<=@RowCount)
begin
delete from B where 同A条件
delete from C where 同A条件
set @RowNow = @RowNow+1
end
end