I have two tables: Import and ImportItem. One Import has multiple ImportItem. One ImportItem has one Import.
In my ImportItem I have a column CountryIDs. It's an NVARCHAR(MAX). It is a string of IDs with ; as delimiter so for example: 3; 4; 5;...
I need to determine duplicates, actually if I run on a duplicate I need to raise some error. So 3; 4; 3; ... 3 is duplicate.
I have a split function (I did not write that function, I was told to use it) that splits a string (nvarchar) and returns a table with columns ItemNo and Item (from previous example it would return 1 for ItemNo and 3 for Item, 2 for ItemNo and 4 for Item.
I need to write a procedure. My question is, how can I iterate through that column of CountryIDs for given ImportItem ID and split it and check for duplicates?
You can use apply to get the list of items, something like this:
select ii.itemno, s.c, count(*) as cnt
from importitem ii apply
dbo.split(ii.countryids) as s(c)
group by ii.itemno, s.c
having count(*) > 1;
Once you have the list, you can decide how to process it in the stored procedure.
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句