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