SQL Delete Where Not In

Question

I have a relation mapping table like this:

attributeid bigint
productid bigint

To clean relations that are not used any more, I want to delete all recors where productid = x and attributeid not in (@includedIds), like the following example:

@attributetypeid bigint, 
@productid bigint,
@includedids varchar(MAX)  


DELETE FROM reltable 
WHERE productid = @productid AND 
attributetypeid = @attributetypeid AND 
attributeid NOT IN (@includedids);

When running the SQL with the includedids param containing more than 1 id - like this: 25,26 - I get a SqlException saying:

Error converting data type varchar to bigint.

And that is of course due to the , in that varchar(max) param...

How should I construct my delete statement to make it work?

Answer 1

  SET QUOTED_IDENTIFIER ON
  GO
  CREATE FUNCTION [dbo].[ListToTable] (
  /*
  FUNCTION ListToTable
  Usage: select entry from listtotable('abc,def,ghi') order by entry desc
  PURPOSE: Takes a comma-delimited list as a parameter and returns the values of that list into a table variable.
  */
  @mylist varchar(8000)
  )
  RETURNS @ListTable TABLE (
  seqid int not null,
  entry varchar(255) not null)

  AS

  BEGIN
      DECLARE 
          @this varchar(255), 
          @rest varchar(8000),
          @pos int,
          @seqid int

      SET @this = ' '
      SET @seqid = 1
      SET @rest = @mylist
      SET @pos = PATINDEX('%,%', @rest)
      WHILE (@pos > 0)
      BEGIN
          set @this=substring(@rest,1,@pos-1)
          set @rest=substring(@rest,@pos+1,len(@rest)-@pos)
          INSERT INTO @ListTable (seqid,entry)  VALUES (@seqid,@this)
          SET @pos= PATINDEX('%,%', @rest)
          SET @seqid=@seqid+1
      END
      set @this=@rest
      INSERT INTO @ListTable (seqid,entry) VALUES (@seqid,@this)
      RETURN 
  END

Run that script in your SQL Server database to create the function ListToTable. Now, you can rewrite your query like so:

@attributetypeid bigint, 
@productid bigint,
@includedids varchar(MAX)  


DELETE FROM reltable 
WHERE productid = @productid AND 
attributetypeid = @attributetypeid AND 
attributeid NOT IN (SELECT entry FROM ListToTable(@includedids));

Where @includedids is a comma delimited list that you provide. I use this function all the time when working with lists. Keep in mind this function does not necessarily sanitize your inputs, it just looks for character data in a comma delimited list and puts each element into a record. Hope this helps.

Answer 2

Joel Spolsky answered a very similar question here: Parameterize an SQL IN clause

You could try something similar, making sure to cast your attributetypeid as a varchar.

Answer 3

You can't pass a list as an parameter (AFAIK).

Can you rewrite the sql to use a subquery, something like this:

delete from reltable
WHERE productid = @productid AND 
attributetypeid = @attributetypeid AND 
attributeid NOT IN (select id from ... where ... );

?