how to deleted records using IN clause

Question

I have a Stored Proc that inserts record but taking a comma separated string and parsing it and adding individual records

     Create PROCEDURE [dbo].[Save_List_PMIDS]
     @MemberID INT=  88888,
     @PMIDList VARCHAR(MAX)= '12345,56789,67432'

     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].[Publication.Person.Include]
      --WHERE (MemberID = @MemberID) AND PMID IN (@PMIDList);

INSERT INTO [dbo].[Publication.Person.Include]
    ( PersonID, PMID, MemberID )
    SELECT  @MemberID, PMID, @MemberID
    FROM    #pmids

    DROP TABLE #pmids

My problem is with the delete statement. How can I convert the PMIDList string to numbers to use in the IN list so I don't add duplicates?

So it would look like

  DELETE FROM [dbo].[Publication.Person.Include]
  WHERE (MemberID = @MemberID) AND PMID IN (12345,56789,67432);

Thanks in advance

Possible duplicate of [Passing a varchar full of comma delimited values to a SQL Server IN function](http://stackoverflow.com/questions/878833/passing-a-varchar-full-of-comma-delimited-values-to-a-sql-server-in-function) — AndreFeijo, Mar 10 '17 at 03:07
Possible duplicate of [SQL in (@Variable) query](http://stackoverflow.com/questions/10333319/sql-in-variable-query) — SQLMason, Mar 10 '17 at 03:21

Gordon Linoff · Accepted Answer · 2017-03-10T03:17:51.637

2

Why would you use the string? Your code has just put the ids in a table:

DELETE FROM [dbo].[Publication.Person.Include]
    WHERE MemberID = @MemberID AND
          PMID IN (SELECT PMID FROM #pmids);

edited Mar 10 '17 at 03:17

answered Mar 10 '17 at 03:08

Gordon Linoff

1,242,037
58
646
786

Your right-- I was making it harder that it needed to be. Is your code right where is says (Select PMID IN #pmids); I had to change it to Select PMID From #pmid) – Bill Mar 10 '17 at 03:16
Thanks again for your help – Bill Mar 10 '17 at 03:28

how to deleted records using IN clause

1 Answers1