curiousBoy curiousBoy - 1 year ago 87
SQL Question

Is it possible to contain duplicated ids in calculation with IN() statement - TSQL?

I am working on an SP that gets the sold product ids as comma separated string as an input.


  • Cannot add a new input

  • Cannot change the input datatype

I have a function that gets the inputted comma separated string and returns a table including a row for each id.

SELECT * FROM SplitStringIDsIntoTable(@ProductIDs,',')

Then to be able to calculate the cost of good of each sold item (if the item sold 3 times, I need to add its COG 3 times and unfortunately it is out of question to get a new input that tells me the quantity for each sold item), I was doing something like:

SET x = (SELECT SUM(p.COG) FROM Products p WHERE p.ProductId IN
(SELECT * FROM SplitStringIDsIntoTable(@ProductIDs,',')))

But the problem here is, I do not have the quantity as input.
When I use IN() statement, it counts each id ONLY once as expected.

For instance lets say my sold product ids inputted as '11,12,13,11,13'

My sql statement would be:

SET x = (SELECT SUM(p.COG) FROM Products p WHERE p.ProductId IN

which returns exact same result with :

SET x = (SELECT SUM(p.COG) FROM Products p WHERE p.ProductId IN

which does not give the correct total.
What I am looking for is, is there any statement that takes duplicated values into consideration as well?

I do not want to use While Loop.

PS: I am not sure how to ask this question in title, so question title may look meaningless. Feel free to update it.

Answer Source

Use JOIN instead of IN:

FROM Products p JOIN
     dbo.SplitStringIDsIntoTable(@ProductIDs, ',') ss
     ON p.ProductId = ss.ProductId;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download