SQL Server - Cascading DELETE with Recursive Foreign Keys

Necromancing.
There's 2 simple solutions.

• You can either read Microsoft's sorry-excuse(s) of why they didn't implement this (because it is difficult and time-consuming - and time is money), and explanation of why you don't/shouldn't need it (although you do), and implement the delete-function with a cursor in a stored procedure
  • because you don't really need delete cascade, because you always have the time to change ALL your and ALL of OTHER people's code (like interfaces to other systems) everywhere, anytime, that deletes an employee (or employees, note: plural) (including all superordinate and subordinate objects [including when a or several new ones are added]) in this database (and any other copies of this database for other customers, especially in production when you don't have access to the database [oh, and on the test system, and the integration system, and local copies of production, test, and integration]

or

• you can use a proper DBMS that actually supports recursive cascaded deletes, like PostGreSQL (as long as the graph is directed, and non-cyclic; else ERROR on delete).

PS:
That's sarcasm.

---

Note:

As long as your delete does not stem from a cascade, and you just want to perform a delete on a self-referencing table, you can delete any entry, as long as you remove all subordinate objects as well in the in-clause.

So to delete such an object, do the following:

;WITH CTE AS 
(
    SELECT id, boss_id, [name] FROM employee
    -- WHERE boss_id IS NULL 
    WHERE id = 2 -- <== this here is the id you want to delete !

    UNION ALL

    SELECT employee.id, employee.boss_id, employee.[name] FROM employee
    INNER JOIN CTE ON CTE.id = employee.boss_id 
)
DELETE FROM employee 
WHERE employee.id IN (SELECT id FROM CTE)

Assuming you have the following table structure:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.employee') AND type in (N'U'))
BEGIN
CREATE TABLE dbo.employee
(
    id int NOT NULL,
    boss_id int NULL,
    [name] varchar(50) NULL,
    CONSTRAINT PK_employee PRIMARY KEY ( id )
); 
END
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'dbo.FK_employee_employee') AND boss_id_object_id = OBJECT_ID(N'dbo.employee'))
ALTER TABLE dbo.employee  WITH CHECK ADD  CONSTRAINT FK_employee_employee FOREIGN KEY(boss_id)
REFERENCES dbo.employee (id)
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'dbo.FK_employee_employee') AND boss_id_object_id = OBJECT_ID(N'dbo.employee'))
ALTER TABLE dbo.employee CHECK CONSTRAINT FK_employee_employee
GO

The below might work for you (I haven't tested it so it may require some tweaking). Seems like all you have to do is delete the employees from the bottom of the hierarchy before you delete the ones higher-up. Use a CTE to build the delete hierarchy recursively and order the CTE output descending by the hierarchy level of the employee. Then delete in order.

CREATE PROC usp_DeleteEmployeeAndSubordinates (@empId INT)
AS

;WITH employeesToDelete AS (
    SELECT  id, CAST(1 AS INT) AS empLevel
    FROM    employee
    WHERE   id = @empId
    UNION ALL
    SELECT  e.id, etd.empLevel + 1
    FROM    employee e
            JOIN employeesToDelete etd ON e.boss_id = etd.id AND e.boss_id != e.id
)
SELECT  id, ROW_NUMBER() OVER (ORDER BY empLevel DESC) Ord
INTO    #employeesToDelete
FROM    employeesToDelete;

DECLARE @current INT = 1, @max INT = @@ROWCOUNT;

WHILE @current <= @max
BEGIN
    DELETE employee WHERE id = (SELECT id FROM #employeesToDelete WHERE Ord = @current);
    SET @current = @current + 1;
END;
GO