SQL Delete if not in

Question

I have a relationship mapping table as follows:

attribute bigint
productid bigint

To clear relationships that are no longer in use, I want to remove all recors where productid = x and attributeid are not in (@includedIds), as in 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 starting SQL with an includeids parameter containing more than 1 id - like this: 25.26 - I get a SqlException:

Error converting varchar data type to bigint.

And this, of course, is due to the fact that in the varchar (max) parameter ...

How do I create a 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 the script in the SQL Server database to create the ListToTable function. Now you can rewrite your request like this:

 @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 the comma separated list you provide. I use this function all the time when working with lists. Keep in mind that this function does not necessarily sanitize your inputs; it simply searches for character data in a comma-separated list and puts each item in the entry. Hope this helps.

Answer 2

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

You can try something like this, making sure you write your typeypeid attribute as varchar.

Answer 3

You cannot pass a list as a parameter (AFAIK).

Is it possible to rewrite 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 4

This comma-delimited list can be sent to a user-defined function that will return it as a simple table. Then this table can be requested by your NOT IN. If you need fn, I can provide .. It has been around 5 years since I used sql a lot and I will have to vacuum this part of my brain.

Answer 5

Erland has the ultimate guide to working with lists in a table in SQL 2005, SQL 2008 gives you table-based options .

On a side note, I would avoid the NOT IN pattern for large lists, because it does not scale, but instead looks at the use of left joins.