而循环查询则使异常内存不足

Question

我需要遍历表结果中的所有数据。例如，选择结果得到一个列调用数量。我需要列值来执行循环。但是我在循环过程中出现内存不足异常。

DECLARE @Qty int
DECLARE @COUNT int
DECLARE @itemcode nvarchar(30)
DECLARE @GRNNo nvarchar(30)
DECLARE @UOM nvarchar(30)
DECLARE @Weight decimal(18,2)
DECLARE @PurchaseDate datetime
DECLARE @Cost decimal(18,2)
SET @COUNT = 1
WHILE exists (Select * from #tempOpening) 
BEGIN
  SELECT top 1 @Qty = QTY, @itemcode = ItemCode, @GRNNo = GRNNo, @Cost=UnitPrice
    , @PurchaseDate=PurchaseDate, @UOM=UOM, @Weight = Weight
  from #tempOpening order by ItemCode
  print @Qty
  print @itemcode
  WHILE @Qty>0
  BEGIN
    SELECT @itemcode AS ItemCode,
      'D'+RIGHT('00000000'+CAST(@COUNT as nvarchar(8)),8) as SerialNo,
      @PurchaseDate PurchaseDate,
      '' TestData,
      ''TestRemark,
      'HQ' Location,
      @UOM BuyType,
      1 qty,
      @Weight Weight,
      @Cost Cost,
      0 PackingFee,
      '' Remarks,
      @GRNNo as GRNNO, 
      null returnDate,
      'SATO' createdBy,
      GETDATE() createDate,
      null CreatedDate,
      null ModifiedDate,
      null SaNo,
      @Qty OriginalQty,
      @Weight
    SET @COUNT = @COUNT+1
    SET @Qty = @Qty -1
  END
  print 'Out loop'
  DELETE FROM #tempOpening where ItemCode= @itemcode and GRNNo = @GRNNo
END

Answer 1

试试这个：如果这对你有用，我可以解释所有的事情：

SET NOCOUNT ON
DECLARE @Qty int
DECLARE @COUNT int
DECLARE @itemcode nvarchar(30)
DECLARE @GRNNo nvarchar(30)
DECLARE @UOM nvarchar(30)
DECLARE @Weight decimal(18,2)
DECLARE @PurchaseDate datetime
DECLARE @Cost decimal(18,2)
--Decalre basic table 
DECLARE @tempOpening TABLE 
(
    Id int IDENTITY(1,1),
    QTY int,
    ItemCode nvarchar(30),
    GRNNo nvarchar(30),
    UnitPrice decimal(18,2),
    PurchaseDate datetime,
    UOM nvarchar(30),
    Weight decimal(18,2)
)
DECLARE @site_value INT;
SET @site_value = 0;
--insert test data into basic table
WHILE @site_value <= 4752 
BEGIN
   INSERT INTO @tempOpening(QTY, ItemCode, GRNNo, UnitPrice, PurchaseDate, UOM, Weight)
   VALUES(10,'Code100' + CAST(@site_value AS VARCHAR(10)),'GRNNo',10.2,GETDATE(), 'Unit',5.5);
   SET @site_value = @site_value + 1;
END;
--created a new table for avoiding inner loop
DECLARE @tblQty TABLE
(
    Id INT IDENTITY(1,1),
    RowNo   INT
)
DECLARE @maxQty INT 
SELECT @maxQty =MAX(QTY) FROM @tempOpening
DECLARE @i INT = 1;
--insert data into Qty table with max Qty, this table data will help us to do dynamic query with joining 
WHILE @i <= @maxQty
BEGIN   
   INSERT INTO @tblQty(RowNo)
   VALUES(@i)
   SET @i = @i + 1;
END;
SET @COUNT = 1
--created a temp table to insert all inner loop data, this is the final output data
DECLARE @tblAllData TABLE
(
    Id INT IDENTITY(1,1),
    ItemCode NVARCHAR(30),
    SerialNo NVARCHAR(30),
    PurchaseDate DATETIME,
    TestData NVARCHAR(100),
    TestRemark NVARCHAR(100),
    Location NVARCHAR(100),
    BuyType NVARCHAR(30),
    Qty INT,
    Weight DECIMAL(18,2),
    Cost DECIMAL(18,2),
    PackingFee  INT,
    Remarks NVARCHAR(200),
    GRNNO NVARCHAR(30),
    ReturnDate DATETIME NULL,
    CreatedBy NVARCHAR(100),
    CreateDate DATETIME NULL,
    CreatedDate DATETIME NULL,
    ModifiedDate DATETIME NULL,
    SaNo    INT NULL,
    OriginalQty INT
)
DECLARE C CURSOR FOR 
    SELECT QTY, ItemCode, GRNNo, UnitPrice , PurchaseDate, UOM, Weight from @tempOpening  
OPEN C
FETCH NEXT FROM C INTO @Qty , @itemcode , @GRNNo , @Cost , @PurchaseDate, @UOM , @Weight 
WHILE @@FETCH_STATUS = 0 
BEGIN
    --insert into the all table data
    INSERT INTO @tblAllData(ItemCode, SerialNo, PurchaseDate, TestData, TestRemark, Location, BuyType, Qty, Weight, Cost, PackingFee, Remarks, GRNNO, ReturnDate, 
    CreatedBy, CreateDate, CreatedDate, ModifiedDate, SaNo, OriginalQty)
    SELECT TOP (@Qty) @itemcode AS ItemCode,
      'D'+RIGHT('00000000'+CAST((@COUNT + tbl.RowNo) as nvarchar(8)),8) as SerialNo,
      @PurchaseDate PurchaseDate, '' TestData, ''TestRemark, 'HQ' Location,  @UOM BuyType, 1 qty, @Weight,  @Cost Cost, 0 PackingFee, '' Remarks,
      @GRNNo as GRNNO,  null returnDate, 'SATO' createdBy, GETDATE() createDate, null CreatedDate, null ModifiedDate, null SaNo, @Qty - tbl.RowNo AS OriginalQty
      FROM @tblQty tbl
      ORDER BY tbl.RowNo
    FETCH NEXT FROM C INTO @Qty , @itemcode , @GRNNo , @Cost , @PurchaseDate, @UOM , @Weight   
END
CLOSE C
DEALLOCATE C    
SELECT * FROM @tblAllData