Delete SQL stored procedures for infinitus!

Create procedure [DBO]. [category_delete] @ categoryid int as -- delete [category] -- where categoryid = @ categoryid -- delete [category] -- where fatherid = @ categoryid declare @ Taba table ([Tabid] [int] not null) insert @ Taba (Tabid) Select categoryid from category where fatherid = @ categoryiddeclare @ ID intwhile (exists (select top 1 * From @ Taba )) -- as long as there is data, continue to loop begin select top 1 @ ID = Tabid from @ Taba -- retrieve a record from the variable table insert @ Taba (Tabid) -- Insert the sub-record with the parent ID equal to @ ID into the record select categoryid from category where fatherid = @ ID Delete from category where categoryid = @ ID -- delete the record from the original table delete from @ Taba where tabid = @ ID -- delete the record from the variable table, because it is unnecessary for end Delete [category] Where categoryid = @ categoryid to retrieve all its subrecords. This segment can be used in an infinitely hierarchical table.

Table Design: