Styjun

I need to loop through all the data from the table result. For example select result got a column call Quantity. I need the Column value to do the looping. But I get out of memory exception during looping.

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

Try this: if this work for you I can explain all the things:

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