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