CREATE Procedure Insert_To_Table3AS BEGIN TRANSACTION Insert_To_Table3 DECLARE @Quantity1 int DECLARE @Quantity2 int DECLARE @Temp int DECLARE @Temp2 int Begin set nocount on
declare Cursor1 cursor for SELECT Qnty1 FROM table1 order by Qnty1 open Cursor1 fetch next from Cursor1 into @Quantity1 while @@FETCH_STATUS=0 Begin
declare Cursor2 cursor for select Quty2 from table2 order by Quty2 open Cursor2 fetch next from Cursor2 into @Quantity2 while @@FETCH_STATUS=0 Begin if(@Quantity1>@Quantity2) begin set @Temp=@Quantity1-@Quantity2 insert into Table3(Quty) values(@Quantity2) fetch next from Cursor2 into @Quantity2 if(@Temp<@Quantity2) begin insert into Table3(Quty) values(@Temp) set @Temp2=@Quantity2-@Temp end end else begin fetch next from Cursor2 into @Quantity2 end End
CLOSE Cursor1 DEALLOCATE Cursor1 fetch next from Cursor1 into @Quantity1 End CLOSE Cursor1 DEALLOCATE Cursor1 End if @@error<>0 Begin raiserror('排序优化失败,请与开发商联系!',16,1) RollBack Transaction Insert_To_Table3 Return 99 endCommit Transaction TransOrderOptimize GO
BEGIN TRANSACTION Insert_To_Table3
DECLARE @Quantity1 int
DECLARE @Quantity2 int
DECLARE @Temp int
DECLARE @Temp2 int
Begin
set nocount on
declare Cursor1 cursor for SELECT Qnty1 FROM table1 order by Qnty1
open Cursor1
fetch next from Cursor1 into @Quantity1
while @@FETCH_STATUS=0
Begin
declare Cursor2 cursor for select Quty2 from table2 order by Quty2
open Cursor2
fetch next from Cursor2 into @Quantity2
while @@FETCH_STATUS=0
Begin
if(@Quantity1>@Quantity2)
begin
set @Temp=@Quantity1-@Quantity2
insert into Table3(Quty) values(@Quantity2)
fetch next from Cursor2 into @Quantity2
if(@Temp<@Quantity2)
begin
insert into Table3(Quty) values(@Temp)
set @Temp2=@Quantity2-@Temp
end
end
else
begin
fetch next from Cursor2 into @Quantity2
end
End
CLOSE Cursor1
DEALLOCATE Cursor1 fetch next from Cursor1 into @Quantity1
End CLOSE Cursor1
DEALLOCATE Cursor1
End
if @@error<>0
Begin
raiserror('排序优化失败,请与开发商联系!',16,1)
RollBack Transaction Insert_To_Table3
Return 99
endCommit Transaction TransOrderOptimize
GO