在网上看到很多人对于递归表的删除是使用关系进行的,但是我在SqlServer2005 死活说我违反约束,郁闷啊,于是想到使用递归来做。一下是一张递归表
好了,以下是我的存储过程
执行就可以了。
在调整存储过程时候,发现好像递归的深度也是有限的——32。
CREATE
TABLE
[
dbo
]
.
[
Tiku
]
(
[ TikuID ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ TikuName ] [ varchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[ TikuParentID ] [ int ] NOT NULL ,
CONSTRAINT [ PK_Tiku ] PRIMARY KEY CLUSTERED
(
[ TikuID ] ASC
) WITH (IGNORE_DUP_KEY = OFF ) ON [ PRIMARY ]
) ON [ PRIMARY ]
[ TikuID ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ TikuName ] [ varchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[ TikuParentID ] [ int ] NOT NULL ,
CONSTRAINT [ PK_Tiku ] PRIMARY KEY CLUSTERED
(
[ TikuID ] ASC
) WITH (IGNORE_DUP_KEY = OFF ) ON [ PRIMARY ]
) ON [ PRIMARY ]
好了,以下是我的存储过程
CREATE PROCEDURE [dbo].[DeleteTiku]
@TikuID int
AS
BEGIN
declare @TikuParentID int ;
set @TikuParentID = - 1 ;
select @TikuParentID = TikuID from dbo.Tiku where TikuParentID = @TikuID;
if ( @TikuParentID <> - 1 )
exec DeleteTiku @TikuParentID;
delete from dbo.Tiku where TikuParentID = @TikuID;
END
@TikuID int
AS
BEGIN
declare @TikuParentID int ;
set @TikuParentID = - 1 ;
select @TikuParentID = TikuID from dbo.Tiku where TikuParentID = @TikuID;
if ( @TikuParentID <> - 1 )
exec DeleteTiku @TikuParentID;
delete from dbo.Tiku where TikuParentID = @TikuID;
END
执行就可以了。
在调整存储过程时候,发现好像递归的深度也是有限的——32。