SQl Server ÎÊÌâÇóÖú

RoleName          RoleID  ParentID
ϵͳ¹ÜÀíÔ± 1 0
¹ÜÀíÔ±1         2 1
¹ÜÀíÔ±2         3 1
¹ÜÀíÔ±11         4 2
¹ÜÀíÔ±22         5 3
¹ÜÀíÔ±33         6 3
¹ÜÀíÔ±111          7 4

ÎÒÏÖÔÚÏëʵÏÖ¼¶ÁªÉ¾³ý
ɾ³ý¹ÜÀíÔ±1µÄ»° ¹ÜÀíÔ±11£¬¹ÜÀíÔ±111 Ò²Òª¸ú×Åɾ³ý
ÇëÎÊÕâ¸ö´æ´¢¹ý³ÌÔõôд°¡£¿

---

ÂÛ̳̫¶àÁË

---

ÎÒÒ²¿´µ½¹ý£¬µ«Ò»ÏÂ×ÓÍüÁË£¬Çë°ïÖú

---

ËÑË÷ÏÂBOM

---

SQL code:
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL DROP TABLE #TEMP
IF OBJECT_ID('PROC_DELETE') IS NOT NULL DROP PROCEDURE PROC_DELETE
GO
CREATE TABLE TB(RoleName VARCHAR(50),ROLEID INT,PARENTID INT)
INSERT INTO TB
SELECT 'ϵͳ¹ÜÀíÔ±', 1, 0 UNION ALL
SELECT '¹ÜÀíÔ±1', 2, 1 UNION ALL
SELECT '¹ÜÀíÔ±2', 3, 1 UNION ALL
SELECT '¹ÜÀíÔ±11', 4, 2 UNION ALL
SELECT '¹ÜÀíÔ±22', 5, 3 UNION ALL
SELECT '¹ÜÀíÔ±33', 6, 3 UNION ALL
SELECT '¹ÜÀíÔ±111', 7, 4
GO
CREATE PROCEDURE PROC_DELETE(@ID INT)
AS
BEGIN
SELECT ROLEID INTO #TEMP from TB WHERE ROLEID=@ID
DELETE from TB WHERE ROLEID=@ID
WHILE EXISTS(SELECT 1 from TB WHERE PARENTID IN (SELECT ROLEID from #TEMP))
BEGIN
INSERT I