得到一棵树 取自表内自递归（即ID 与ParentID）
不多言 先看两个生成的实例
Create FUNCTION [f_Get_DownCorpTree] (@CorpID int)
Returns @CorpTree TABLE
(
CorpID int
)
As
Begin
--调用方法：Select * From f_Get_DownCorpTree(20)
--得到公司的下一级公司集合
--SET NOCOUNT ON
--CREATE TABLE [Org_Corp] (
-- [ID] [int] IDENTITY (1, 1) NOT NULL ,
-- [ParentID] [int] NULL ,
-- [CorpCode] [nvarchar] (255) NOT NULL ,
-- [CorpName] [nvarchar] (255) NULL
-- CONSTRAINT [Org_Corp_PK] PRIMARY KEY CLUSTERED
-- (
-- [ID]
-- ) ON [PRIMARY]
--) ON [PRIMARY]
--GO
DECLARE @Cnt int
Declare @i int
Declare @tmpnode int
Declare @stack Table (node int)
Declare @stackTmp Table (node int)
Declare @stackTmpXXX Table(node int)
insert into @stack
select [ID] from vRef_Org_Corp Where ParentID = @CorpID
insert into @stackTmp
select [ID] from vRef_Org_Corp Where ParentID = @CorpID
select @tmpnode = @CorpID
select @Cnt = count(*) from vRef_Org_Corp
select @i = 0
loops:
declare cur cursor for select node from @stackTmp
open cur
fetch next from cur into @tmpnode
while @@FETCH_STATUS = 0
begin
insert into @stack select [ID] from vRef_Org_Corp Where ParentID = @tmpnode
insert into @stackTmpXXX select [ID] from vRef_Org_Corp Where ParentID = @tmpnode
select @i = @i + 1
fetch next from cur into @tmpnode
end
CLOSE cur
DEALLOCATE cur
select @i = @i + 1
delete from @stackTmp
insert into @stackTmp select node from @stackTmpXXX
delete from @stackTmpXXX
if (@i >= @Cnt * 1.5 )
goto ends
goto loops
ends:
Insert into @CorpTree (CorpID)
Select node From @stack
--select * from [Org_Corp]
--Where exists
--(
--Select 1 from @CorpTree Where CorpID = [Org_Corp].[ID]
--)
Return
End
-------------------
Create FUNCTION [f_Get_UpCorpTree] (@CorpID int)
Returns @CorpTree TABLE
(
CorpID int
)
As
Begin
--调用方法：Select * From f_Get_UpCorpTree(8)
--Select * from Org_Corp
--Declare @CorpID int
--Select @CorpID = 9
--Declare @CorpTree Table (CorpID int, flag varchar(10))
--CREATE TABLE [Org_Corp] (
-- [ID] [int] IDENTITY (1, 1) NOT NULL ,
-- [ParentID] [int] NULL ,
-- [CorpCode] [nvarchar] (255) NOT NULL ,
-- [CorpName] [nvarchar] (255) NULL
-- CONSTRAINT [Org_Corp_PK] PRIMARY KEY CLUSTERED
-- (
-- [ID]
-- ) ON [PRIMARY]
--) ON [PRIMARY]
--GO
Declare @TmpCorpID int
Select @TmpCorpID = @CorpID
--得到公司的上一级公司
GetUpCorp:
Select @TmpCorpID = [ParentID] From [vRef_Org_Corp] Where [ID] = @TmpCorpID
if (@TmpCorpID is not null)
Begin
--Print Convert(varchar(10),@TmpCorpID)
Insert into @CorpTree (CorpID) Values (@TmpCorpID)
goto GetUpCorp
End
Return
End
给出相应的生成该类结果的存储过程:
Create Procedure [dbo].[pCreateFunction_DownTree](@Table_Obj nvarchar(200))
As
Begin
Declare @SQL nvarchar(2000)
Select @SQL = '
Create FUNCTION f_Get_Down<%Table_Obj%>Tree (@ObjID int)
Returns @<%Table_Obj%>Tree TABLE
(
[ObjID] int not null
)
As
Begin
--调用方法：Select * From f_Get_Down<%Table_Obj%>Tree(20)
/*
Select *
From <%Table_Obj%>
Where ID in
(
Select ObjID From f_Get_Down<%Table_Obj%>Tree(1)
)
*/
DECLARE @Cnt int
Declare @i int
Declare @tmpnode int
Declare @stack Table (node int)
Declare @stackTmp Table (node int)
Declare @stackTmpXXX Table(node int)
insert into @stack
select [ID] from <%Table_Obj%> Where ParentID = @ObjID
insert into @stackTmp
select [ID] from <%Table_Obj%> Where ParentID = @ObjID
select @tmpnode = @ObjID
select @Cnt = count(*) from <%Table_Obj%>
select @i = 0
loops:
declare cur cursor for select node from @stackTmp
open cur
fetch next from cur into @tmpnode
while @@FETCH_STATUS = 0
begin
insert into @stack select [ID] from <%Table_Obj%> Where ParentID = @tmpnode
insert into @stackTmpXXX select [ID] from <%Table_Obj%> Where ParentID = @tmpnode
select @i = @i + 1
fetch next from cur into @tmpnode
end
CLOSE cur
DEALLOCATE cur
select @i = @i + 1
delete from @stackTmp
insert into @stackTmp select node from @stackTmpXXX
delete from @stackTmpXXX
if (@i >= @Cnt * 1.5 )
goto ends
goto loops
ends:
Insert into @Stack(node) Select @ObjID
Insert into @<%Table_Obj%>Tree ([ObjID])
Select node From @stack
Return
End
'
Select @SQL = Replace(@SQL,'<%Table_Obj%>', @Table_Obj)
print @SQL
Execute (@SQL)
End
---------------
Create Procedure [dbo].[pCreateFunction_UpTree](@Table_Obj nvarchar(200))
As
Begin
Declare @SQL nvarchar(2000)
Select @SQL = '
Create FUNCTION [f_Get_Up<%Table_Obj%>Tree] (@ObjID int)
Returns @<%Table_Obj%>Tree TABLE
(
ObjID int
)
As
Begin
--调用方法：Select ObjID From f_Get_Up<%Table_Obj%>Tree(8)
Declare @TmpObjID int
Select @TmpObjID = @ObjID
--得到Obj的上一级Obj
GetUpObj:
Select @TmpObjID = [ParentID] From [<%Table_Obj%>] Where [ID] = @TmpObjID
if (@TmpObjID is not null and @TmpObjID <> @ObjID)
Begin
--Print Convert(varchar(10),@TmpObjID)
Insert into @<%Table_Obj%>Tree (ObjID) Values (@TmpObjID)
goto GetUpObj
End
Return
End
'
Select @SQL = Replace(@SQL,'<%Table_Obj%>', @Table_Obj)
print @SQL
Execute (@SQL)
End
：_）
