LISTING 6: Procedure That Removes an Option from Table AB2

CREATE PROC DeleteOption @ItemId int, @Option varchar(20)
AS
DECLARE @Length int
SELECT @Length = (len(@option)+1)

IF EXISTS
  (SELECT * FROM AB2 WHERE ItemId = @ItemId AND Options LIKE '%,' + @Option + ',%')
  BEGIN
    -- Option exists somewhere mid-string.
    UPDATE ab2
    SET Options = Replace(Options, ',' + @Option + ',', ',')
    WHERE ItemId = @ItemId
  END
IF EXISTS
  (SELECT * FROM AB2 WHERE ItemId = @ItemId AND Options LIKE @Option + ',%')
  BEGIN
    -- Option exists at the beginning of the string.
    UPDATE AB2
    SET Options = Substring(Options, (@Length + 1),Len(Options))
    WHERE ItemId = @ItemId And Options LIKE @Option + ',%'
  END
IF EXISTS
  (SELECT * FROM AB2 WHERE ItemId = @ItemId AND Options LIKE '%,' + @Option)
  BEGIN
    -- Option exists at the end of the string.
    UPDATE AB2
    SET Options = Substring(Options, 1,(Len(Options)-@Length))
    WHERE ItemId = @ItemId AND Options LIKE '%,' + @Option
  END
IF EXISTS (SELECT * FROM AB2 WHERE ItemId = @ItemId AND Options = @Option)
  BEGIN
    UPDATE AB2
    SET Options = NULL WHERE ItemId = @ItemId AND Options = @Option
  END