服务器列表SQL

Create TABLE [dbo].[ServerTree](
[ID] int IDENTITY(1,1),
[ParentID] int NULL,
[Name] varchar (128) COLLATE Chinese_PRC_CI_AS NULL,
[IdCode] int NULL ) ON [PRIMARY]
GO
Create CLUSTERED INDEX [IX_Tree] ON [dbo].[ServerTree]([ParentID]) ON [PRIMARY]
GO
Alter TABLE [dbo].[ServerTree] WITH NOCHECK ADD
CONSTRAINT[PK_ServerTree]PRIMARY KEY NONCLUSTERED
([ID]) ON [PRIMARY],
CONSTRAINT [子ID不能等于父ID] CHECK ([ID] <> [ParentID])
GO
Alter TABLE [dbo].[ServerTree] ADD
CONSTRAINT [FK_ServerTree_Tree] FOREIGN KEY([ParentID]) REFERENCES [dbo].[ServerTree] ([ID])
GO
Create FUNCTION dbo.fGetTreeTable
(@ID int= null)
RETURNS @Tab TABLE(ID int, ParentID int, Name varchar(128), Lev int)
AS
BEGIN
Declare @lev int
Set @lev=0
While @lev=0 or @@ROWCount>0
Begin
Set @Lev=@Lev+1
Insert @Tab(ID, ParentID, Name, Lev)
Select ID, ParentID, Name, @Lev From ServerTree Where (@Lev=1 and ((ParentID=@ID) or (@ID is null and ParentID is null))) or (ParentID in (Select ID From @Tab Where Lev=@Lev-1))
order by ID
End
RETURN
END
GO
Insert ServerTree(ParentID, Name) values(null, '在线会议')
Insert ServerTree(ParentID, Name) values(1, '华东地区')
Insert ServerTree(ParentID, Name) values(1, '西南地区')
Insert ServerTree(ParentID, Name) values(1, '西北地区')
Insert ServerTree(ParentID, Name) values(1, '东部沿海')
GO
Select * from dbo.fGetTreeTable(null)