Deleting multiple rows by ids and checking if those ids are not in other tables

Question

I have a list of ids that i want to delete but i have to check if those ids are not in other tables first.If they are i want to insert those ids in to another list separated by columns. Assuming @keywords to be the list of ids.

SELECT Replace(item,'''','') AS KeywordId 
FROM   Splitdelimiterstring(@keywords,',')IF NOT EXISTS 
( 
       SELECT KeywordId 
       FROM   ContentKeyword 
       WHERE  KeywordId = ?? 
       UNION 
       SELECT KeywordId 
       FROM   JobKeyword 
       WHERE  KeywordId = ?? 
       UNION 
       SELECT KeywordId 
       FROM   CategoryGroup 
       WHERE  KeywordId= ?? ) 
BEGIN 
  DELETE 
  FROM   Keywords 
  WHERE  KeywordId =??
END
  ELSE ??

Answer 1

The first thing you need to to is write a UDF that can split a string into a table. Here is my version.

CREATE  FUNCTION fn_Split(@Text varchar(MAX), @Delimiter varchar(20) = ',')
RETURNS @Strings TABLE
(   
  Position int IDENTITY PRIMARY KEY,
  Value varchar(MAX)  
)
AS
BEGIN

DECLARE @Index int = -1

WHILE (LEN(@Text) > 0)
  BEGIN 
    SET @Index = CHARINDEX(@Delimiter , @Text) 

    IF (@Index = 0) AND (LEN(@Text) > 0) 
      BEGIN  
        INSERT INTO @Strings VALUES (@Text)
          BREAK 
      END 

    IF (@Index > 1) 
      BEGIN  
        INSERT INTO @Strings VALUES (LEFT(@Text, @Index - 1))  
        SET @Text = RIGHT(@Text, (LEN(@Text) - @Index)) 
      END 
    ELSE
      SET @Text = RIGHT(@Text, (LEN(@Text) - @Index))

    END
  RETURN
END

Next step is to write a select statement that selects only those ID's from your string that IS NOT present in either of your tabels (ContentKeyword, JobKeyword, CategoryGroup). That's fairly simple with the above function in hand. Simply do a LEFT JOIN on the function and check if ID of the joined table is null:

select s.Value from dbo.fn_Split('1,2,3,4,5', ',') s
  left outer join ContentKeyword CK on CK.KeywordId = s.Value
  left outer join JobKeyword     JK on JK.KeywordId = s.Value
  left outer join CategoryGroup  JG on JG.KeywordId = s.Value
where 
  (1 = 1)
  and CK.KeywordId is null 
  and JK.KeywordId Is null 
  and JG.KeywordId is null

EDIT

Here is my complete test script:

if exists (select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME = 'fn_Split' )
  drop function [dbo].[fn_Split]
go
CREATE  FUNCTION fn_Split(@Text varchar(MAX), @Delimiter varchar(20) = ',')
RETURNS @Strings TABLE
(   
  Position int IDENTITY PRIMARY KEY,
  Value varchar(MAX)  
)
AS
BEGIN

DECLARE @Index int = -1

WHILE (LEN(@Text) > 0)
  BEGIN 
    SET @Index = CHARINDEX(@Delimiter , @Text) 

    IF (@Index = 0) AND (LEN(@Text) > 0) 
      BEGIN  
        INSERT INTO @Strings VALUES (@Text)
          BREAK 
      END 

    IF (@Index > 1) 
      BEGIN  
        INSERT INTO @Strings VALUES (LEFT(@Text, @Index - 1))  
        SET @Text = RIGHT(@Text, (LEN(@Text) - @Index)) 
      END 
    ELSE
      SET @Text = RIGHT(@Text, (LEN(@Text) - @Index))      
    END
  RETURN
END
go

if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'ContentKeyword')
  drop table ContentKeyword;
go
Create Table ContentKeyword  
(
    [KeywordId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Name] [nvarchar](500) NOT NULL
)
go

if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'ContentKeyword')
  drop table JobKeyword;
go
Create Table JobKeyword 
(
    [KeywordId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Name] [nvarchar](500) NOT NULL
)
go
if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'ContentKeyword')
  drop table CategoryGroup;
go
Create Table CategoryGroup 
(
    [KeywordId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Name] [nvarchar](500) NOT NULL
)
go
  --Insert dummy data

  insert into CategoryGroup(Name)  values('First value')
go

select S.Value from dbo.fn_Split('1,2,3,4,5', ',') S
  left outer join ContentKeyword CK on CK.KeywordId = s.Value
  left outer join JobKeyword     JK on JK.KeywordId = s.Value
  left outer join CategoryGroup  JG on JG.KeywordId = s.Value
where 
  (1 = 1)
  and CK.KeywordId is null 
  and JK.KeywordId Is null 
  and JG.KeywordId is null