I have a hierarchy of menu and sub menus setup using this table:
CREATE TABLE menu
(
id INT NOT NULL IDENTITY PRIMARY KEY,
name VARCHAR(30) NOT NULL,
parentID INT NULL --Null If Root Menu
)
I want all sub menus to be deleted when a parent menu is deleted. I
wasn't able to get a recursive procedure to work because I got an error
about multiple cursors w/ the same name.
The best I've come up w/ is this:
DELETE FROM menu WHERE id = x --Del Menu
--Cleanup Children
DELETE FROM menu WHERE parentID <> NULL AND parentID NOT IN(SELECT id
from menu)
Is there a better/faster way of doing this?