Last post May 03, 2012 10:55 PM by Decker Dong - MSFT

Member

9 Points

46 Posts

### Looping Through a Results Set

Hi All,

Im in need of a bit of help since my team lead is out. Im trying to figure out how to manipulate my results set that is returned from my initial query. Which is below here. How do I go about looping through and identifying where the same part number would have multiple entries, and if so then covert down to the smallest unit of measure? Im assuming a loop but I have not yet written one of those yet in SQL.

```SELECT
l.partNum AS [Part Number]
, l.pricePerUOM AS [Price Per UOM]
, l.uom AS UOM
,SUM(CONVERT(NUMERIC(18,12), l.totalCostPerWS/l.pricePerUOM)) AS [Quantity/WS]
FROM tbl_step s
JOIN tbl_line l ON s.stepId = l.stepId
WHERE s.revisionId = 150
AND ISNUMERIC(l.totalCostPerWS) > 0
AND ISNUMERIC(l.pricePerUOM) > 0
GROUP BY l.partNum, l.pricePerUOM, l.uom
ORDER BY l.partNum,l.uom```
`My Psuedo Code:`

--2. Loop through the data already established(temp table?), and identify quart, milliliter, liter, and gallon.
--If the same part number has multiple entries  (1 milliliter, 1  liter)
--Ex: Liter
--(Quantity/WS) * 1000 (result A) Converting from liter to milliliter.
--(Quantity/WS)for the same part number but milliliters(result B)added to the result from above. (result A + result B)= result C
--(result C) * Price Per UOM = Final Answer
--If there is only one entry, for either liter or milliliter then leave it alone.

--going from liter to milliliter * 1000
--going from gallon to liter * 3.7854118

--Declare 3 variables? (resultA, resultB, resultC?)

Any suggestions would be greatly appreciated.

All-Star

94120 Points

18123 Posts

### Re: Looping Through a Results Set

May 03, 2012 10:55 PM|Decker Dong - MSFT|LINK

Hello：）

I think the problem seems to be a little complicated……So you can use SQL View to create a temporary datatable and then use SqlCommand to fetch all the things out，and then use foreach to loop the DataTable.Rows and then use if statements to do what you want.