SQL Delete Where Not In

Question

Hi

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

Joel Spolsky answered a very similar question here: http://stackoverflow.com/questions/337704/parameterizing-a-sql-in-clause/337817#337817

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

Answer 2

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 ... );

?

Answer 3

That comma delimited list can be sent to a user defined function which will return it as a simple table. That table can then be queried by your NOT IN. If you need the fn I can provide.. It's been about 5 yrs since I used sql much and I'll have to dust off that section of my brain..

Answer 4

  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 5

Erland has the definitive guide for dealing with lists to table in SQL 2005, SQL 2008 gives you table based params.

On a side note I would avoid a NOT IN pattern for large lists, cause it does not scale, instead look at using left joins.