这是我在项目中用到的一个存储过程,用来通过当前节点ID递归查找其所有的子节点。需要申明的是本存储过程仅支持SQL Server 2005
--
建表
GO
/* ***** 对象: Table [dbo].[sys_Group] 脚本日期: 01/17/2008 16:29:33 ***** */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [ dbo ] . [ sys_Group ] (
[ GroupID ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ G_CName ] [ nvarchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ G_ParentID ] [ int ] NOT NULL DEFAULT (( 0 )),
[ G_ShowOrder ] [ int ] NOT NULL DEFAULT (( 0 )),
[ G_Level ] [ int ] NULL ,
[ G_ChildCount ] [ int ] NULL ,
[ G_Delete ] [ tinyint ] NULL ,
CONSTRAINT [ PK_SYS_GROUP ] PRIMARY KEY CLUSTERED
(
[ GroupID ] ASC
) WITH (IGNORE_DUP_KEY = OFF ) ON [ PRIMARY ]
) ON [ PRIMARY ]
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 分类ID号 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' sys_Group ' , @level2type = N ' COLUMN ' , @level2name = N ' GroupID '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 分类中文说明 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' sys_Group ' , @level2type = N ' COLUMN ' , @level2name = N ' G_CName '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 上级分类ID0:为最高级 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' sys_Group ' , @level2type = N ' COLUMN ' , @level2name = N ' G_ParentID '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 显示顺序 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' sys_Group ' , @level2type = N ' COLUMN ' , @level2name = N ' G_ShowOrder '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 当前分类所在层数 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' sys_Group ' , @level2type = N ' COLUMN ' , @level2name = N ' G_Level '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 当前分类子分类数 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' sys_Group ' , @level2type = N ' COLUMN ' , @level2name = N ' G_ChildCount '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 是否删除1:是0:否 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' sys_Group ' , @level2type = N ' COLUMN ' , @level2name = N ' G_Delete '
GO
/* ***** 对象: Table [dbo].[sys_Group] 脚本日期: 01/17/2008 16:29:33 ***** */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [ dbo ] . [ sys_Group ] (
[ GroupID ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ G_CName ] [ nvarchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ G_ParentID ] [ int ] NOT NULL DEFAULT (( 0 )),
[ G_ShowOrder ] [ int ] NOT NULL DEFAULT (( 0 )),
[ G_Level ] [ int ] NULL ,
[ G_ChildCount ] [ int ] NULL ,
[ G_Delete ] [ tinyint ] NULL ,
CONSTRAINT [ PK_SYS_GROUP ] PRIMARY KEY CLUSTERED
(
[ GroupID ] ASC
) WITH (IGNORE_DUP_KEY = OFF ) ON [ PRIMARY ]
) ON [ PRIMARY ]
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 分类ID号 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' sys_Group ' , @level2type = N ' COLUMN ' , @level2name = N ' GroupID '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 分类中文说明 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' sys_Group ' , @level2type = N ' COLUMN ' , @level2name = N ' G_CName '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 上级分类ID0:为最高级 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' sys_Group ' , @level2type = N ' COLUMN ' , @level2name = N ' G_ParentID '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 显示顺序 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' sys_Group ' , @level2type = N ' COLUMN ' , @level2name = N ' G_ShowOrder '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 当前分类所在层数 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' sys_Group ' , @level2type = N ' COLUMN ' , @level2name = N ' G_Level '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 当前分类子分类数 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' sys_Group ' , @level2type = N ' COLUMN ' , @level2name = N ' G_ChildCount '
GO
EXEC sys.sp_addextendedproperty @name = N ' MS_Description ' , @value = N ' 是否删除1:是0:否 ' , @level0type = N ' SCHEMA ' , @level0name = N ' dbo ' , @level1type = N ' TABLE ' , @level1name = N ' sys_Group ' , @level2type = N ' COLUMN ' , @level2name = N ' G_Delete '
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)