存储过程递归实现

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetNodeTable]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNodeTable]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbTest]
GO

CREATE TABLE [dbo].[tbTest] (
 [id] [int] NULL ,
 [upperid] [int] NULL ,
 [name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

----创建存储过程
CREATE proc GetNodeTable
@nodeid int,
@nodename varchar(50)
as
if(@nodeid=0)
 select @nodeid=id from tbTest where name=@nodename

declare  @nodetable table(nodeid int,nodename varchar(50),nextnodeid int)

insert @nodetable select a.id as 'nodeid' ,a.name,a.upperid from tbTest as a where id = @nodeid
while @@rowcount > 0
begin
 insert @nodetable
 select a.id,a.name,a.upperid
 --from tbTest a,@nodetable b
 --where a.upperid = b.nodeid and  a.id not in(select @nodeid from  @nodetable)
 from tbTest a,@nodetable b
 where a.upperid = b.nodeid and  a.id not in(select nodeid from  @nodetable)
end
select * from @nodetable

--exec GetNodeTable 1,''

--select * from tbTest

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO