Jobin Joseph Jobin Joseph - 2 months ago 11
SQL Question

Query inserting multiple times using cursor

Hi I'have the below result for cursor

PONumber Code QuantityReceived
0200073 200757 4
0200073 201052 2
0200073 201435 2
0200073 200757 10
0200073 201056 1
0200073 202200 2


the procedure I'm using is

BEGIN
DECLARE @PN nvarchar(30);
DECLARE @TCODE nvarchar(50);
DECLARE @REQNTY INT;
DECLARE @LOTID nvarchar(50);
DECLARE @QUANTITY nvarchar(50);

DECLARE CUR CURSOR FOR
SELECT PONumber,Code,QuantityReceived
FROM Temp_stock2
--GROUP BY PONumber,Code,QuantityReceived

OPEN CUR

FETCH NEXT FROM CUR INTO @PN,@TCODE,@REQNTY
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN
WHILE(@REQNTY>0)
BEGIN
SELECT TOP(1)@LOTID=RR1038_LotEntry.ID,@QUANTITY=RR1038_LotEntry.Quantity
FROM [dbo].RR1038_LotEntry
INNER JOIN Temp_stock2 on Temp_stock2.LotID=RR1038_LotEntry.ID
WHERE RR1038_LotEntry.Quantity>0 AND Temp_stock2.Code=@TCode
ORDER BY RR1038_LotEntry.Quantity DESC;

IF (@QUANTITY >= @REQNTY)
BEGIN
-- UPDATING LOT ENTRY TABLE
UPDATE [dbo].RR1038_LotEntry
SET RR1038_LotEntry.Quantity = RR1038_LotEntry.Quantity-@REQNTY,LastUpdated=GETDATE()
FROM [dbo].RR1038_LotEntry
INNER JOIN Temp_stock2 on Temp_stock2.LotID=RR1038_LotEntry.ID
WHERE RR1038_LotEntry.ID =@LOTID AND Temp_stock2.Code=@TCode;

-- INSERTING THE SAME DATA TO LOT HISTORY TABLE
INSERT INTO [dbo].RR1038_LotHistory
(StoreID,LotEntryId,Quantity,ReferenceNumber,ReferenceType,LastUpdated)
SELECT
0 as StoreID ,Temp_stock2.LotID,
@REQNTY,
Temp_stock2.PurchaseOrderid as ReferenceNumber,
3 as ReferenceType,GETDATE() as LastUpdated
FROM [dbo].RR1038_LotEntry
INNER JOIN Temp_stock2 on Temp_stock2.LotID=RR1038_LotEntry.ID
WHERE RR1038_LotEntry.ID =@LOTID AND Temp_stock2.Code=@TCode;

SET @REQNTY=0;

END
ELSE
BEGIN
-- INSERTING THE SAME DATA TO LOT HISTORY TABLE
INSERT INTO [dbo].RR1038_LotHistory
(StoreID,LotEntryId,Quantity,ReferenceNumber,ReferenceType,LastUpdated)
SELECT
0 as StoreID ,Temp_stock2.LotID,
@REQNTY,
Temp_stock2.PurchaseOrderid as ReferenceNumber,
3 as ReferenceType,GETDATE() as LastUpdated
FROM [dbo].RR1038_LotEntry
INNER JOIN Temp_stock2 on Temp_stock2.LotID=RR1038_LotEntry.ID
WHERE RR1038_LotEntry.ID =@LOTID AND Temp_stock2.Code=@TCode;

SET @REQNTY=@REQNTY-@QUANTITY;

-- UPDATING LOT ENTRY TABLE
UPDATE [dbo].RR1038_LotEntry
SET RR1038_LotEntry.Quantity = 0,LastUpdated=GETDATE()
FROM [dbo].RR1038_LotEntry
INNER JOIN Temp_stock2 on Temp_stock2.LotID=RR1038_LotEntry.ID
WHERE RR1038_LotEntry.ID =@LOTID AND Temp_stock2.Code=@TCode;
END
END
END

FETCH NEXT FROM CUR INTO @PN,@TCODE,@REQNTY
END
CLOSE CUR
DEALLOCATE CUR
END


While I'm executing the procedure I'm getting duplicate insertion for a specific row for the CODE exists twice.

Output
how can I avoid the duplicate entry in the result set?

Answer

Made some modification in the query as below, now working fine,

DECLARE CUR CURSOR FOR
SELECT PONumber,Code,QuantityReceived ,LotID
FROM Temp_stock2 


OPEN CUR

FETCH NEXT FROM CUR INTO @PN,@TCODE,@REQNTY,@LOTID
WHILE @@FETCH_STATUS = 0   
BEGIN 
BEGIN

    -- UPDATING  LOT ENTRY TABLE
    UPDATE [dbo].RR1038_LotEntry
    SET RR1038_LotEntry.Quantity = RR1038_LotEntry.Quantity-@REQNTY,LastUpdated=GETDATE()
    FROM [dbo].RR1038_LotEntry
    INNER JOIN   Temp_stock2 on Temp_stock2.LotID=RR1038_LotEntry.ID 
    WHERE RR1038_LotEntry.ID =@LOTID AND Temp_stock2.Code=@TCode;

    -- INSERTING THE SAME DATA TO LOT HISTORY TABLE
    INSERT INTO [dbo].RR1038_LotHistory 
    (StoreID,LotEntryId,Quantity,ReferenceNumber,ReferenceType,LastUpdated)
    SELECT 
    0 as StoreID ,Temp_stock2.LotID,
    @REQNTY,
    Temp_stock2.PurchaseOrderid  as ReferenceNumber,
    3 as ReferenceType,GETDATE() as LastUpdated
    FROM [dbo].RR1038_LotEntry
    INNER JOIN   Temp_stock2 on Temp_stock2.LotID=RR1038_LotEntry.ID 
    WHERE RR1038_LotEntry.ID =@LOTID AND Temp_stock2.Code=@TCode;
END

FETCH NEXT FROM CUR INTO @PN,@TCODE,@REQNTY,@LOTID
END
CLOSE CUR 
DEALLOCATE CUR
Comments