Delete records from table which ids is NOT based on a list of ids (which is nvarchar) separated by comma

0
votes

I need your help regarding the following situation in SQL Server 2008R2.

In SQL, I have a list of ids saved as NVARCHAR(MAX) like N('1,2,3,4,5')

I read this How to convert comma separated NVARCHAR to table records in SQL Server 2005?

In this scope, I wrote a function for this:

ALTER FUNCTION [dbo].[func$intlist_to_tbl] (@list nvarchar(MAX))
   RETURNS @tbl TABLE (number int NOT NULL) AS
BEGIN
   DECLARE @pos        int,
           @nextpos    int,
           @valuelen   int

   SELECT @pos = 0, @nextpos = 1

   WHILE @nextpos > 0
   BEGIN
      SELECT @nextpos = charindex(',', @list, @pos + 1)
      SELECT @valuelen = CASE WHEN @nextpos > 0
                              THEN @nextpos
                              ELSE len(@list) + 1
                         END - @pos - 1
      INSERT @tbl (number)
         VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
      SELECT @pos = @nextpos
   END
  RETURN
END

Ok, I want to delete some records which id IS NOT found in above list.

How to do that ?

I tried something like:

DELETE a_ FROM TableA a_
LEFT JOIN func$intlist_to_tbl(@idList) _tmp
ON _tmp.number = a_.ID_
WHERE a_.ID IS NULL

But is not correct.

1
sqlsql-server
If you store only digits - why on earth do you think you need NVARCHAR ?? Total waste of space (actually - you're just using twice as much storage as really needed.....) NVARCHAR is Unicode, 2 bytes per character (or digit), and is only needed if you want to store things like Arabic, Hebrew, Chinese, Japanese etc. characters ... - marc_s
@marc_s: No, I have a big stored procedure and I store some ids in a list. Perhaps I have to store in a temporary table instead using nvarchar. Am I right ? - Snake Eyes

1 Answers

0
votes

Maybe something similar?

DELETE FROM
  TableA a_
where
  a_.id not in (select number from func$intlist_to_tbl(@idList))