CREATE PROCEDURE sp_drop_dtscategory
  @name_like sysname,
  @id UNIQUEIDENTIFIER = NULL,
  @flags INT = 0           --// Bitmask:  0x01 == recursive (drop all subcategories and packages)
AS
  SET NOCOUNT ON

  --// Temp table in case recursion is needed.
  DECLARE @recurse TABLE (id UNIQUEIDENTIFIER, passcount INT DEFAULT(0))

  IF (@name_like IS NOT NULL)
  BEGIN
    INSERT @recurse (id) SELECT id FROM sysdtscategories WHERE name LIKE @name_like
    IF @@rowcount = 0
    BEGIN
      RAISERROR(14262, 16, 1, '@name_like', @name_like)
      RETURN(1) -- Failure
    END
    IF @@rowcount > 1
    BEGIN
      RAISERROR(14592, 16, -1, @name_like)
      RETURN(1) -- Failure
    END
    SELECT @name_like = name, @id = id FROM sysdtscategories WHERE name LIKE @name_like
  END ELSE BEGIN
    --// Verify the id.  @name_like will be NULL if we're here so no need to initialize.
    SELECT @name_like = name FROM sysdtscategories WHERE id = @id
    IF @name_like IS NULL
    BEGIN
      DECLARE @stringfromclsid NVARCHAR(200)
      SELECT @stringfromclsid = CONVERT(NVARCHAR(50), @id)
      RAISERROR(14262, 16, 1, '@id', @stringfromclsid)
      RETURN(1) -- Failure
    END
    INSERT @recurse (id) VALUES (@id)
  END

  --// We now have a unique category.

  --// Cannot drop the predefined categories (or the root, which already failed above as IID_NULL
  --// is not an id in sysdtscategories).  These will be at top level.
  IF @id IN (
    'B8C30000-A282-11d1-B7D9-00C04FB6EFD5'
    , 'B8C30001-A282-11d1-B7D9-00C04FB6EFD5'
    , 'B8C30002-A282-11d1-B7D9-00C04FB6EFD5'
  ) BEGIN
      RAISERROR(14598, 16, 1)
      RETURN(1) -- Failure
  END

  --// Check for subcategories or packages.
  IF EXISTS (SELECT * FROM sysdtspackages WHERE categoryid = @id)
             OR EXISTS (SELECT * FROM sysdtscategories WHERE parentid = @id)
  BEGIN
    --// It does.  Make sure recursion was requested.
    IF (@flags & 0x01 = 0)
    BEGIN
      RAISERROR(14593, 16, -1, @name_like)
      RETURN(1) -- Failure
    END

    --// Fill up @recurse.
    UPDATE @recurse SET passcount = 0
    WHILE (1 = 1)
    BEGIN
      UPDATE @recurse SET passcount = passcount + 1
      INSERT @recurse (id, passcount)
        SELECT c.id, 0 FROM sysdtscategories c INNER JOIN @recurse r ON c.parentid = r.id
        WHERE passcount = 1
      IF @@rowcount = 0
        BREAK
    END
  END

  DELETE sysdtspackages FROM sysdtspackages INNER JOIN @recurse r ON sysdtspackages.categoryid = r.id
  DELETE sysdtscategories FROM sysdtscategories INNER JOIN @recurse r ON sysdtscategories.id = r.id

  RETURN(0) -- SUCCESS