Custom cascade delete stored procedure for temporal tables in SQL Server

Preface:

I want to create a stored procedure in SQL Server (2016 - v13.0) to manually perform cascading deletes across tables with foreign key constraints, including self-referencing tables. I require a custom implementation due to the use of temporal tables, which do not support cascading deletes. I find this limitation very frustrating, especially given that temporal tables are designed to track deletions. In order to delete a row, it is necessary first to delete all rows that depend on it.

I should be clear: I do not want to touch data in history tables, I only care about deleting rows in the current tables.

My knowledge of SQL isn't very deep, and I'm not sure if this is the best approach. I've considered using soft deletes, but I'm not fond of them due to the clutter they introduce and the need to be diligent in maintaining data accuracy. I've heard of others suggesting a stored procedure for performing deletions explicitly, but in my scenario I have far too many tables to implement a deletion procedure for each individually; instead, I need a dynamic one that will work for any given table row.

Objective:

The stored procedure should:

• Accept three parameters: @SchemaName (schema name), @TableName (table name), and @Id (primary key of the entity to delete).
• Delete the specified row and any rows that depend on it, respecting all foreign key constraints.
• Properly handle self-referencing tables by deleting child rows before parent rows. This is possibly where things venture into scary territory.
• Operate within a single transaction.

Example Scenario:

Consider this hyper-simplified representation of the database I'm working with.

Expectations:

Given this hierarchy, if I attempt to delete a Folder, the procedure should recursively delete all dependent ElementInstances, Elements, Pages, Forms, and child Folders (in that order).

Questions:

• Is it feasible to implement a stored procedure to handle this kind of dynamic cascading delete operation for temporal tables?
• How can I efficiently handle recursive deletions, especially with self-referencing tables, without running into recursion depth issues?

Any guidance or examples would be greatly appreciated!

I decided to write a deletion procedure for each table. Below is an example from the application I'm building, and isn't 1:1 with the example given in my OP, but it follows the same principles.

Omitting many tables for the sake of brevity, assume all temporal.

Here's the procedure to delete the top-level entity, which has a self reference. It'll first locate all of it's child Folders and execute the DeleteFolder procedure on it to begin a sort of recursive process, then it'll delete the Forms belonging to them via the DeleteForm procedure.

DeleteFolder.sql

CREATE PROCEDURE [dbo].[DeleteFolder]
    @FolderId INT
AS
BEGIN
    SET NOCOUNT ON;

    -- Delete child folders
    DECLARE @ChildFolderId INT;
    DECLARE ChildFoldersCursor CURSOR FOR
    SELECT [Id] FROM [ui].[Folders] WHERE [ParentId] = @FolderId;

    OPEN ChildFoldersCursor;
    FETCH NEXT FROM ChildFoldersCursor INTO @ChildFolderId;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC [dbo].[DeleteFolder] @ChildFolderId;
        FETCH NEXT FROM ChildFoldersCursor INTO @ChildFolderId;
    END

    CLOSE ChildFoldersCursor;
    DEALLOCATE ChildFoldersCursor;

    -- Delete related forms
    DECLARE @FormId INT;
    DECLARE FormsCursor CURSOR FOR
    SELECT [Id] FROM [config].[Forms] WHERE [FolderId] = @FolderId;

    OPEN FormsCursor;
    FETCH NEXT FROM FormsCursor INTO @FormId;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC [dbo].[DeleteForm] @FormId;
        FETCH NEXT FROM FormsCursor INTO @FormId;
    END

    CLOSE FormsCursor;
    DEALLOCATE FormsCursor;

    -- Delete the folder
    DELETE FROM [ui].[Folders] WHERE [Id] = @FolderId;
END

Here's the procedure that is called in the previous one. It details how to delete the entities that depend on the given Form. Again, it continues to use stored procedures for deletions instead of explicit row deletions.

DeleteForm.sql

CREATE PROCEDURE [dbo].[DeleteForm]
    @FormId INT
AS
BEGIN
    SET NOCOUNT ON;

    -- Delete related pages
    DECLARE @PageId INT;
    DECLARE PagesCursor CURSOR FOR
    SELECT [Id] FROM [ui].[Pages] WHERE [FormId] = @FormId;

    OPEN PagesCursor;
    FETCH NEXT FROM PagesCursor INTO @PageId;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC [dbo].[DeletePage] @PageId;
        FETCH NEXT FROM PagesCursor INTO @PageId;
    END

    CLOSE PagesCursor;
    DEALLOCATE PagesCursor;

    -- Delete related lookup lists
    DECLARE @LookupListId INT;
    DECLARE LookupListsCursor CURSOR FOR
    SELECT [Id] FROM [config].[LookupLists] WHERE [FormId] = @FormId;

    OPEN LookupListsCursor;
    FETCH NEXT FROM LookupListsCursor INTO @LookupListId;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC [dbo].[DeleteLookupList] @LookupListId;
        FETCH NEXT FROM LookupListsCursor INTO @LookupListId;
    END

    CLOSE LookupListsCursor;
    DEALLOCATE LookupListsCursor;

    -- Delete the related Elements
    DECLARE @ElementId INT;
    DECLARE ElementsCursor CURSOR FOR
    SELECT [Id] FROM [config].[Elements] WHERE [FormId] = @FormId;

    OPEN ElementsCursor;
    FETCH NEXT FROM ElementsCursor INTO @ElementId;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC [dbo].[DeleteElement] @ElementId;
        FETCH NEXT FROM ElementsCursor INTO @ElementId;
    END

    CLOSE ElementsCursor;
    DEALLOCATE ElementsCursor;

    -- Delete the form
    DELETE FROM [config].[Forms] WHERE [Id] = @FormId;
END

Eventually, we get to a point where we're deleting leafs that don't depend on anything, and thus we've emulated ON DELETE CASCADE, with a bit more flexibility and (unfortunately) a lot more code.