1 USE db  2 GO  3   4 /****** Object:  Table [dbo].[cx_Navigation]    Script Date: 10/23/2014 22:36:28 ******/  5 SET ANSI_NULLS ON  6 GO  7   8 SET QUOTED_IDENTIFIER ON  9 GO 10  11 CREATE TABLE [dbo].[cx_Navigation]( 12     [ID] [int] NOT NULL, 13     [NavType] [tinyint] NOT NULL, 14     [Name] [nvarchar](64) NOT NULL, 15     [Title] [nvarchar](128) NOT NULL, 16     [SubTitle] [nvarchar](128) NOT NULL, 17     [LinkUrl] [nvarchar](256) NOT NULL, 18     [SortID] [int] NOT NULL, 19     [IsLock] [bit] NOT NULL, 20     [Remark] [nvarchar](512) NOT NULL, 21     [ParentID] [int] NOT NULL, 22     [ClassList] [nvarchar](512) NOT NULL, 23     [ClassLayer] [int] NOT NULL, 24     [ChannelID] [int] NOT NULL, 25     [ActionType] [nvarchar](512) NOT NULL, 26     [IsSystem] [bit] NOT NULL, 27  CONSTRAINT [PK_cx_NAVIGATION] PRIMARY KEY CLUSTERED  28 ( 29     [ID] ASC 30 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] 31 ) ON [PRIMARY] 32  33 GO 34  35 EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘自增ID‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘cx_Navigation‘, @level2type=N‘COLUMN‘,@level2name=N‘ID‘ 36 GO 37  38 EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘导航类别‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘cx_Navigation‘, @level2type=N‘COLUMN‘,@level2name=N‘NavType‘ 39 GO 40  41 EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘导航ID‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘cx_Navigation‘, @level2type=N‘COLUMN‘,@level2name=N‘Name‘ 42 GO 43  44 EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘标题‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘cx_Navigation‘, @level2type=N‘COLUMN‘,@level2name=N‘Title‘ 45 GO 46  47 EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘副标题‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘cx_Navigation‘, @level2type=N‘COLUMN‘,@level2name=N‘SubTitle‘ 48 GO 49  50 EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘链接地址‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘cx_Navigation‘, @level2type=N‘COLUMN‘,@level2name=N‘LinkUrl‘ 51 GO 52  53 EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘排序数字‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘cx_Navigation‘, @level2type=N‘COLUMN‘,@level2name=N‘SortID‘ 54 GO 55  56 EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘是否隐藏0显示1隐藏‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘cx_Navigation‘, @level2type=N‘COLUMN‘,@level2name=N‘IsLock‘ 57 GO 58  59 EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘备注说明‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘cx_Navigation‘, @level2type=N‘COLUMN‘,@level2name=N‘Remark‘ 60 GO 61  62 EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘所属父导航ID‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘cx_Navigation‘, @level2type=N‘COLUMN‘,@level2name=N‘ParentID‘ 63 GO 64  65 EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘菜单ID列表(逗号分隔开)‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘cx_Navigation‘, @level2type=N‘COLUMN‘,@level2name=N‘ClassList‘ 66 GO 67  68 EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘导航深度‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘cx_Navigation‘, @level2type=N‘COLUMN‘,@level2name=N‘ClassLayer‘ 69 GO 70  71 EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘所属频道ID‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘cx_Navigation‘, @level2type=N‘COLUMN‘,@level2name=N‘ChannelID‘ 72 GO 73  74 EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘权限资源‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘cx_Navigation‘, @level2type=N‘COLUMN‘,@level2name=N‘ActionType‘ 75 GO 76  77 EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘系统默认‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘cx_Navigation‘, @level2type=N‘COLUMN‘,@level2name=N‘IsSystem‘ 78 GO 79  80 EXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=N‘系统导航菜单‘ , @level0type=N‘SCHEMA‘,@level0name=N‘dbo‘, @level1type=N‘TABLE‘,@level1name=N‘cx_Navigation‘ 81 GO 82  83 ALTER TABLE [dbo].[cx_Navigation] ADD  CONSTRAINT [DF_cx_Navigation_NavType]  DEFAULT ((0)) FOR [NavType] 84 GO 85  86 ALTER TABLE [dbo].[cx_Navigation] ADD  CONSTRAINT [DF_cx_Navigation_Name]  DEFAULT (‘‘) FOR [Name] 87 GO 88  89 ALTER TABLE [dbo].[cx_Navigation] ADD  CONSTRAINT [DF_cx_Navigation_Title]  DEFAULT (‘‘) FOR [Title] 90 GO 91  92 ALTER TABLE [dbo].[cx_Navigation] ADD  CONSTRAINT [DF_cx_Navigation_SubTitle]  DEFAULT (‘‘) FOR [SubTitle] 93 GO 94  95 ALTER TABLE [dbo].[cx_Navigation] ADD  CONSTRAINT [DF_cx_Navigation_LinkUrl]  DEFAULT (‘‘) FOR [LinkUrl] 96 GO 97  98 ALTER TABLE [dbo].[cx_Navigation] ADD  CONSTRAINT [DF_cx_Navigation_SortID]  DEFAULT ((99)) FOR [SortID] 99 GO100 101 ALTER TABLE [dbo].[cx_Navigation] ADD  CONSTRAINT [DF_cx_Navigation_IsLock]  DEFAULT ((0)) FOR [IsLock]102 GO103 104 ALTER TABLE [dbo].[cx_Navigation] ADD  CONSTRAINT [DF_cx_Navigation_Remark]  DEFAULT (‘‘) FOR [Remark]105 GO106 107 ALTER TABLE [dbo].[cx_Navigation] ADD  CONSTRAINT [DF_cx_Navigation_ParentID]  DEFAULT ((0)) FOR [ParentID]108 GO109 110 ALTER TABLE [dbo].[cx_Navigation] ADD  CONSTRAINT [DF_cx_Navigation_ClassList]  DEFAULT (‘‘) FOR [ClassList]111 GO112 113 ALTER TABLE [dbo].[cx_Navigation] ADD  CONSTRAINT [DF_cx_Navigation_ClassLayer]  DEFAULT ((1)) FOR [ClassLayer]114 GO115 116 ALTER TABLE [dbo].[cx_Navigation] ADD  CONSTRAINT [DF_cx_Navigation_ChannelID]  DEFAULT ((0)) FOR [ChannelID]117 GO118 119 ALTER TABLE [dbo].[cx_Navigation] ADD  CONSTRAINT [DF_cx_Navigation_ActionType]  DEFAULT (‘‘) FOR [ActionType]120 GO121 122 ALTER TABLE [dbo].[cx_Navigation] ADD  CONSTRAINT [DF_cx_Navigation_IsSystem]  DEFAULT ((0)) FOR [IsSystem]123 GO

 1 USE DB 2 GO 3 /****** Object:  StoredProcedure [dbo].[cx_ModifyNavigation]    Script Date: 10/23/2014 22:42:08 ******/ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8 -- ============================================= 9 -- Author:        tommy duan10 -- Create date: 2014-10-2311 -- Description:    编辑导航12 -- =============================================13 CREATE PROCEDURE [dbo].[cx_ModifyNavigation]14     -- Add the parameters for the stored procedure here15     @ID    int,16     @NavType    tinyint,17     @Name    nvarchar(64),18     @Title    nvarchar(128),19     @SubTitle    nvarchar(128),20     @LinkUrl    nvarchar(256),21     @SortID    int,22     @IsLock    bit,23     @Remark    nvarchar(512),24     @ParentID    int,25     @ClassList    nvarchar(512),26     @ClassLayer    int,27     @ChannelID    int,28     @ActionType    nvarchar(512),29     @IsSystem    bit30 AS31 BEGIN32     -- SET NOCOUNT ON added to prevent extra result sets from33     -- interfering with SELECT statements.34     SET NOCOUNT ON;35     36     37     -- 前提：自己不可以修改自己为自己的子节点。38     -- 当前节点被移到了自己的子节点下边(当前节点修改时，选中的父节点为自己之前的子节点)。39     Declare @SelectNodeWasChildNode int;40     Select @SelectNodeWasChildNode=COUNT(1) From cx_Navigation Where ClassList like ‘%,‘+CAST(@ID as nvarchar(32))+‘,%‘ and ID=@ParentID;41     42     If @SelectNodeWasChildNode>0 43     Begin44         -- 查找旧父节点数据45         Declare @OldParentID int;    46         Declare @TempClassLayer int;47         Declare @TempClassList nvarchar(512);48         49         Set @TempClassLayer=1;50         Set @TempClassList=‘,‘+CAST(@ParentID as Nvarchar(32))+‘,‘;51         52         Select @OldParentID=ParentID From cx_Navigation Where ID=@ID;53         54         If @OldParentID>0 55         Begin56             Declare @OldClassLayer int;57             Declare @OldClassList nvarchar(32);58             59             Select @OldClassLayer=ClassLayer,@OldClassList=ClassList From cx_Navigation Where ID=@OldParentID;60             61             Set @TempClassLayer=@OldClassLayer+1;62             Set @TempClassList=@OldClassList+CAST(@ParentID as Nvarchar(32))+‘,‘;63         End64         65         -- 提升被选中作为父节点的节点66         Update cx_Navigation Set ParentID=@OldParentID,ClassList=@TempClassList,ClassLayer=@TempClassLayer67         Where ID=@ParentID;68         69         -- 提升被选中作为父节点的节点的所有子节点。70         Execute cx_ModifyNavigationChildren @ParentID;71     End72     73     -- 修改节点74     If @ParentID>0 75     Begin76         Select @ClassLayer=ClassLayer,@ClassList=ClassList From cx_Navigation Where ID=@ParentID;77         Set @ClassLayer=@ClassLayer+1;78         Set @ClassList=@ClassList+CAST(@ID as nvarchar(32))+‘,‘;79     End80     Else81     Begin82         Set @ClassLayer=1;83         Set @ClassList=‘,‘+CAST(@ID as Nvarchar(32))+‘,‘;84     End85     86     UPDATE [cx_Navigation]87         SET [NavType] = @NavType,[Name] = @Name,[Title] = @Title,[SubTitle] = @SubTitle,[LinkUrl] = @LinkUrl88           ,[SortID] = @SortID,[IsLock] = @IsLock,[Remark] = @Remark,[ParentID] = @ParentID,[ClassList] = @ClassList89           ,[ClassLayer] = @ClassLayer,[ChannelID] = @ChannelID,[ActionType] = @ActionType,[IsSystem] = @IsSystem90     WHERE ID=@ID;91     92     -- 修改自己的子节点93     Execute cx_ModifyNavigationChildren @ID;94 END

递归函数还没有解决不支持临时表的问题，我这样写可能效率不高。

 1 USE DB 2 GO 3 /****** Object:  StoredProcedure [dbo].[cx_ModifyNavigationChildren]    Script Date: 10/23/2014 22:43:49 ******/ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8  9 -- =============================================10 -- Author:        tommy duan11 -- Create date: 2014-10-2312 -- Description:    编辑导航13 -- =============================================14 CREATE PROCEDURE [dbo].[cx_ModifyNavigationChildren]15     -- Add the parameters for the stored procedure here16     @ParentID int17 AS18 Begin    19     -- 验证信息是否存在20     If Exists(SELECT ID From cx_Navigation Where ID=@ParentID)21     Begin22         Declare @ClassLayer int;23         Declare @ClassList nvarchar(512);24         25         -- 获取父节点信息26         SELECT @ClassLayer=ClassLayer,@ClassList=ClassList From cx_Navigation Where ID=@ParentID;27         28         -- 查找当前父节点在所有一级子节点，并修改他们的层次信息        29         Declare @TempClassLayer int;30         Declare @TempClassList nvarchar(512);31         Declare @MaxRowNumber int;32         Declare @Cursor int;33         Declare @ID int;34         Set @Cursor=0;35         SELECT @MaxRowNumber=Count(1) From 36         (37             SELECT ID,ROW_NUMBER()Over(Order By ID) as RowNumber From cx_Navigation Where ParentID=@ParentID38         ) As T10;39         40         While @Cursor<@MaxRowNumber41         Begin42             Set @Cursor=@Cursor+1;43             Select @ID= T10.ID From (44                 SELECT ID,ROW_NUMBER()Over(Order By ID) as RowNumber From cx_Navigation Where ParentID=@ParentID45             ) as T10 Where T10.RowNumber=@Cursor;46             If @ID IS NOT NULL 47             Begin                 48                 -- 修改子节点的ID列表及深度49                 Set @TempClassLayer=@ClassLayer+1;50                 Set @TempClassList=@ClassList+CAST(@ID as nvarchar(32))+‘,‘;51                 52                 Update cx_Navigation Set ClassLayer=@TempClassLayer,ClassList=@TempClassList Where ID=@ID;53 54                 -- 递归调用55                 Execute cx_ModifyNavigationChildren @ID;56             End57         End58     End        59 End