数结构中，节点移动解决方案

我的数据表结构如下：

  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 GO
100 
101 ALTER TABLE [dbo].[cx_Navigation] ADD  CONSTRAINT [DF_cx_Navigation_IsLock]  DEFAULT ((0)) FOR [IsLock]
102 GO
103 
104 ALTER TABLE [dbo].[cx_Navigation] ADD  CONSTRAINT [DF_cx_Navigation_Remark]  DEFAULT ('') FOR [Remark]
105 GO
106 
107 ALTER TABLE [dbo].[cx_Navigation] ADD  CONSTRAINT [DF_cx_Navigation_ParentID]  DEFAULT ((0)) FOR [ParentID]
108 GO
109 
110 ALTER TABLE [dbo].[cx_Navigation] ADD  CONSTRAINT [DF_cx_Navigation_ClassList]  DEFAULT ('') FOR [ClassList]
111 GO
112 
113 ALTER TABLE [dbo].[cx_Navigation] ADD  CONSTRAINT [DF_cx_Navigation_ClassLayer]  DEFAULT ((1)) FOR [ClassLayer]
114 GO
115 
116 ALTER TABLE [dbo].[cx_Navigation] ADD  CONSTRAINT [DF_cx_Navigation_ChannelID]  DEFAULT ((0)) FOR [ChannelID]
117 GO
118 
119 ALTER TABLE [dbo].[cx_Navigation] ADD  CONSTRAINT [DF_cx_Navigation_ActionType]  DEFAULT ('') FOR [ActionType]
120 GO
121 
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 duan
10 -- Create date: 2014-10-23
11 -- Description:    编辑导航
12 -- =============================================
13 CREATE PROCEDURE [dbo].[cx_ModifyNavigation]
14     -- Add the parameters for the stored procedure here
15     @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    bit
30 AS
31 BEGIN
32     -- SET NOCOUNT ON added to prevent extra result sets from
33     -- 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     Begin
44         -- 查找旧父节点数据
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         Begin
56             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         End
64         
65         -- 提升被选中作为父节点的节点
66         Update cx_Navigation Set ParentID=@OldParentID,ClassList=@TempClassList,ClassLayer=@TempClassLayer
67         Where ID=@ParentID;
68         
69         -- 提升被选中作为父节点的节点的所有子节点。
70         Execute cx_ModifyNavigationChildren @ParentID;
71     End
72     
73     -- 修改节点
74     If @ParentID>0 
75     Begin
76         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     End
80     Else
81     Begin
82         Set @ClassLayer=1;
83         Set @ClassList=','+CAST(@ID as Nvarchar(32))+',';
84     End
85     
86     UPDATE [cx_Navigation]
87         SET [NavType] = @NavType,[Name] = @Name,[Title] = @Title,[SubTitle] = @SubTitle,[LinkUrl] = @LinkUrl
88           ,[SortID] = @SortID,[IsLock] = @IsLock,[Remark] = @Remark,[ParentID] = @ParentID,[ClassList] = @ClassList
89           ,[ClassLayer] = @ClassLayer,[ChannelID] = @ChannelID,[ActionType] = @ActionType,[IsSystem] = @IsSystem
90     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 duan
11 -- Create date: 2014-10-23
12 -- Description:    编辑导航
13 -- =============================================
14 CREATE PROCEDURE [dbo].[cx_ModifyNavigationChildren]
15     -- Add the parameters for the stored procedure here
16     @ParentID int
17 AS
18 Begin    
19     -- 验证信息是否存在
20     If Exists(SELECT ID From cx_Navigation Where ID=@ParentID)
21     Begin
22         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=@ParentID
38         ) As T10;
39         
40         While @Cursor<@MaxRowNumber
41         Begin
42             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=@ParentID
45             ) 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             End
57         End
58     End        
59 End

还有其他什么解决方案吗?除了采用代码实现外。