String Split Function In SQL Server 2016

select distinct(value) as Activities from campaigns cross apply string_split(Activity, ',') 

insert into TargetRevenueTypes select targetid, value as RevenueType from Targets cross apply string_split(RevenueTypes, ',') 

select c.campaignid, cast(s.value as int) as Activity from campaigns c  cross apply string_split(c.Activity,',')  s where c.CampaignID = 3788

insert into campaignactivities select campaignid, cast(value as int), 0 from campaigns cross apply string_split(ExcludedActivities,',') 
where ExcludedActivities is not null and not exists (select 1 from campaignactivities ca where ca.CampaignId = campaignid and ca.ActivityId = value) 

Order By the CSV

SELECT story.*
FROM story
JOIN (
    SELECT value, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
    FROM STRING_SPLIT(@SelectedFeatures, ',')
) AS selected ON story.storycode = selected.value
ORDER BY selected.rn

In this query:

1. The STRING_SPLIT() function is used to split the @SelectedFeatures list into individual values.
2. The inner subquery assigns a row number (rn) to each value using the ROW_NUMBER() window function. The ORDER BY (SELECT NULL) trick is used to keep the original order of the values.
3. The outer query then joins the story table with the subquery on the storycode column.
4. Finally, the results are ordered by the rn column, which corresponds to the original order of the values in @SelectedFeatures.

This should give you the result set in the order you expect based on the order of values in the @SelectedFeatures list.

Simple Insert from CSV:

public async Task UpdateMagazineTagsAsync(int tagId, List<int> magazineIds)
{
    var ids = string.Join(",",magazineIds);
    var sql = @"delete from magazinetags where tagid = @tagid;
                insert magazinetags (select @tagId, value from string_split(@ids, ','))";
    await stepCommsConnection.ExecuteAsync(sql, new {tagId, ids});
}