TSQL Cascade delete for child records?

Question

I have a parent/child table (simple tree) table structure (ID, ParentID), where I want to delete (and get the ID of) all children for a given parent ID - similar to this post http://stackoverflow.com/questions/1433808/sql-server-cascade-delete-and-parent-child-table .

During the loop, where I've got the current ID, I will also be performing other actions with this ID.

Can someone give me an example of the SPROC to accomplish this?

Thanks

Answer 1

Assuming, you're on SQL SERVER 2005, here is the example of stored procedure to accomplish this (by using CTE):

CREATE PROCEDURE [DeleteRecordWithChild]
    @id int
AS
BEGIN
    WITH Nodes ([Id], [ParentId], [Level]) 
    AS (
        SELECT  T.[Id], T.[ParentId], 0 AS [Level]
        FROM    [dbo].[YourTable] T
        WHERE   T.[Id] = @id

        UNION ALL

        SELECT  T.[Id], T.[ParentId], N.[Level] + 1
        FROM    [dbo].[YourTable] T
                INNER JOIN Nodes N ON N.[Id] = T.[ParentId]
    )

    DELETE
    FROM    [YourTable]
    OUTPUT  deleted.*
    WHERE   [Id] IN (
        SELECT  TOP 100 PERCENT N.[Id] 
        FROM    Nodes N
        ORDER BY N.[Level] DESC
    );
END

This removes a row defined by @id parameter with all child nodes from the table and returns the deleted values to the processing application in a single action.

You also could return deleted rows into a table variable (should be defined before CTE):

DECLARE @deleted_rows TABLE
(
    [Id] int,
    [ParentId] int,
    [Level] int
);

and then

DELETE
FROM    [YourTable]
OUTPUT  deleted.* INTO @deleted_rows
WHERE   [Id] IN (
    SELECT  TOP 100 PERCENT N.[Id] 
    FROM    Nodes N
    ORDER BY N.[Level] DESC
);