How to loop through list of ID's

问题:

I am getting list of id's in the select statement. How to loop through each id and print below

DECLARE @InventoryID INT
Select @InventoryID = I.InventoryID
From G2owner.LoadSlip L (nolock)
     Inner Join G2owner.LoadSlipReleaseOrderMapping LM (nolock) on L.LoadSlipID = LM.LoadSlipID
     Inner Join G2owner.LoadSlipDetail LSD (nolock) on LM.LoadSlipReleaseOrdermappingID = LSD.LoadSlipReleaseOrdermappingID 
     Inner Join G2owner.Inventory I (nolock) on LSD.InventoryID = I.InventoryID
Where LM.ReleaseOrderID = 7156 and L.LoadSlipID = 3014
      and L.TerminalID = 3 and I.InventoryEndDate IS NULL and I.TotalNetWeight = 0.0000000

PRINT @InventoryID

回答1:

Avoid Cursors or any row by row operation which uses loops if you can as it hinders performance, but if you have to:

DECLARE @InventoryID INT

DECLARE myCursor CURSOR FOR 
SELECT I.InventoryID
From G2owner.LoadSlip L with(nolock)
    Inner Join G2owner.LoadSlipReleaseOrderMapping LM with(nolock) on L.LoadSlipID = LM.LoadSlipID
    Inner Join G2owner.LoadSlipDetail LSD with(nolock) on LM.LoadSlipReleaseOrdermappingID = LSD.LoadSlipReleaseOrdermappingID 
    Inner Join G2owner.Inventory I with(nolock) on LSD.InventoryID = I.InventoryID
Where LM.ReleaseOrderID = 7156 and L.LoadSlipID = 3014
    and L.TerminalID = 3 and I.InventoryEndDate IS NULL and I.TotalNetWeight = 0.0000000

OPEN myCursor
FETCH NEXT FROM myCursor INTO @InventoryID 

WHILE @@FETCH_STATUS = 0
BEGIN

    PRINT (@InventoryID);
   FETCH NEXT FROM myCursor INTO @InventoryID
END
CLOSE myCursor
DEALLOCATE myCursor

Also WITH(nolock) can lead to dirty reads!!!