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

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] [] NULL ,
[upperid] [] NULL ,
[name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) _disibledevent=>select @nodeid=id from tbTest where name=@nodename

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

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