SQl Server 问题求助

Question

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 也要跟着删除
请问这个存储过程怎么写啊？

Answer 1

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 INTO #TEMP
SELECT ROLEID  FROM TB WHERE PARENTID IN (SELECT ROLEID FROM #TEMP)
DELETE FROM TB WHERE PARENTID IN (SELECT ROLEID FROM #TEMP)
END
END
GO
EXEC PROC_DELETE 2
SELECT * FROM TB
/*
系统管理员 1 0
管理员2 3 1
管理员22 5 3
管理员33 6 3
*/

Answer 2

if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (RoleName nvarchar(12),RoleID int,ParentID int)
insert into [TB]
select N'系统管理员',1,0 union all
select N'管理员1',2,1 union all
select N'管理员2',3,1 union all
select N'管理员11',4,2 union all
select N'管理员22',5,3 union all
select N'管理员33',6,3 union all
select N'管理员111',7,4
;WITH tab(RoleID, ParentID,RoleName) AS
   (SELECT root.RoleID, 
           root.parentid, 
           root.RoleName
   FROM tb root
   WHERE Roleid=2
   UNION ALL
   SELECT sub.RoleID, 
          sub.parentid, 
          sub.RoleName
   FROM tb sub, tab super
   WHERE sub.parentid = super.RoleID
   )
delete from tb where RoleID in(
select RoleID from tab
)
select * from tb
/*
RoleName     RoleID      ParentID
------------ ----------- -----------
系统管理员        1           0
管理员2         3           1
管理员22        5           3
管理员33        6           3(4 個資料列受到影響)*/

Answer 3

if object_id('tb') is not null drop table tb 
go
create table tb(RoleName varchar(10),RoleID int,ParentID int)
insert 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';with cte as
(select a.RoleID from tb a where a.RoleID='2'
union all 
select c.RoleID from tb c,cte b where b.RoleID=ParentID)
delete tb where RoleID in(select RoleID from cte)
select * from tb/*(7 行受影响)(3 行受影响)
RoleName   RoleID      ParentID
---------- ----------- -----------
系统管理员      1           0
管理员2       3           1
管理员22      5           3
管理员33      6           3(4 行受影响)*/

Answer 4

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-12-10 09:35:26
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([RoleName] varchar(10),[RoleID] int,[ParentID] int)
insert [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
--------------开始查询--------------------------
;with f as
(
select
 a.RoleID from tb a where a.RoleID='2'
union all 
select
 c.RoleID from tb c,f b where b.RoleID=ParentID
)
delete
 tb 
where 
  RoleID in(select RoleID from f)
select * from tb----------------结果----------------------------
/* RoleName   RoleID      ParentID
---------- ----------- -----------
系统管理员      1           0
管理员2       3           1
管理员22      5           3
管理员33      6           3(4 行受影响)
*/

Answer 5

上面写错了，用这个：
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,0 AS 'FLAG' 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 INTO #TEMP
SELECT ROLEID,1  FROM TB WHERE PARENTID IN (SELECT ROLEID FROM #TEMP)
DELETE FROM TB WHERE PARENTID IN (SELECT ROLEID FROM #TEMP WHERE FLAG=0) 
UPDATE #TEMP SET FLAG=0
END
END
GO
EXEC PROC_DELETE 2
SELECT * FROM TB
/*
系统管理员 1 0
管理员2 3 1
管理员22 5 3
管理员33 6 3
*/

Answer 6

学习了！
                SMJ  2009年12月10日12:51:57