I know that this question has been asked many times over many years and I have read every one of them but I wanted to get a modern solution since software has changed and String_Split has been introduced in SQL Server 2016.

I have a stored procedure that splits a comma-separated string and inserts the values into a table after it deletes the value if it is already there. The code works but sometimes I write code that is totally convoluted.

ALTER PROCEDURE [dbo].[Admin_Save_PMIDS]
    @MemberID INT,
    @PMIDList VARCHAR(MAX)
AS
BEGIN
    SELECT Split.a.value('.', 'VARCHAR(100)') AS PMID
    INTO #pmids
    FROM   
        (SELECT    
             CAST('<M>' + REPLACE(@PMIDList, ',', '</M><M>') + '</M>' AS XML) AS String) AS A
    CROSS APPLY 
        String.nodes('/M') AS Split (a)   

    DELETE FROM [dbo].[PublicationTable]
    WHERE (MemberID = @MemberID) 
     AND  PMID IN (SELECT PMID FROM #pmids)

    INSERT INTO [dbo].[PublicationTable] (PersonID, PMID, MemberID)
        SELECT        
            @MemberID, PMID, @MemberID
        FROM            
            [#pmids]
        WHERE        
            (LEN(PMID) > 0)

    DROP TABLE #pmids
END

As you can see I'm creating a tmp table and using XML structure as a cross apply. The code works but since I'm doing a lot of updating in the application I figured I'd get some current opinions.

Thanks

I know that this question has been asked many times over many years and I have read every one of them but I wanted to get a modern solution since software has changed and String_Split has been introduced in SQL Server 2016.

I have a stored procedure that splits a comma-separated string and inserts the values into a table after it deletes the value if it is already there. The code works but sometimes I write code that is totally convoluted.

ALTER PROCEDURE [dbo].[Admin_Save_PMIDS]
    @MemberID INT,
    @PMIDList VARCHAR(MAX)
AS
BEGIN
    SELECT Split.a.value('.', 'VARCHAR(100)') AS PMID
    INTO #pmids
    FROM   
        (SELECT    
             CAST('<M>' + REPLACE(@PMIDList, ',', '</M><M>') + '</M>' AS XML) AS String) AS A
    CROSS APPLY 
        String.nodes('/M') AS Split (a)   

    DELETE FROM [dbo].[PublicationTable]
    WHERE (MemberID = @MemberID) 
     AND  PMID IN (SELECT PMID FROM #pmids)

    INSERT INTO [dbo].[PublicationTable] (PersonID, PMID, MemberID)
        SELECT        
            @MemberID, PMID, @MemberID
        FROM            
            [#pmids]
        WHERE        
            (LEN(PMID) > 0)

    DROP TABLE #pmids
END

As you can see I'm creating a temporary table and using an XML structure as a cross apply. The code works but since I'm doing a lot of updating in the application I figured I'd get some current opinions.