create procedure ssp_GetTree @Tablename varchar(40), @nodeid int as declare @sql varchar(8000)set @sql=' DECLARE @TempTable1 TABLE (nodeid int) INSERT @TempTable1 VALUES ('+cast(@nodeid as varchar(10))+') WHILE EXISTS (SELECT * FROM '+@TableName+' WHERE father IN (SELECT nodeid FROM @TempTable1) AND nodeid NOT IN (SELECT nodeid FROM @TempTable1)) INSERT @TempTable1 SELECT nodeid FROM '+@TableName+' WHERE father IN (SELECT nodeid FROM @TempTable1) AND nodeid NOT IN (SELECT nodeid FROM @TempTable1) SELECT * FROM @TempTable1'exec (@sql)go
create procedure ssp_GetTree @Tablename varchar(40), @nodeid int as declare @sql varchar(8000)set @sql=' DECLARE @TempTable1 TABLE (nodeid int,father int) INSERT @TempTable1 select nodeid,father from '+@TableName+' where nodeid='+cast(@nodeid as varchar(10))+' WHILE EXISTS (SELECT * FROM '+@TableName+' WHERE father IN (SELECT nodeid FROM @TempTable1) AND nodeid NOT IN (SELECT nodeid FROM @TempTable1)) INSERT @TempTable1 SELECT nodeid,father FROM '+@TableName+' WHERE father IN (SELECT nodeid FROM @TempTable1) AND nodeid NOT IN (SELECT nodeid FROM @TempTable1) SELECT * FROM @TempTable1'exec (@sql)go
create procedure p_test as declare @test table( f_1 char(10)) declare @exec varchar(128)insert into @test values('test ')set @exec ='Select * from @test'exec (@exec)goexec p_test go drop procedure p_test go create procedure p_test as create table #test ( f_1 char(10)) declare @exec varchar(128)insert into #test values('test ')set @exec ='Select * from #test'exec (@exec)goexec p_test godrop procedure p_test
@Tablename varchar(40),
@nodeid int
as
declare @sql varchar(8000)set @sql='
DECLARE @TempTable1 TABLE (nodeid int)
INSERT @TempTable1 VALUES ('+cast(@nodeid as varchar(10))+')
WHILE EXISTS (SELECT * FROM '+@TableName+' WHERE father IN (SELECT nodeid FROM @TempTable1) AND nodeid NOT IN (SELECT nodeid FROM @TempTable1))
INSERT @TempTable1 SELECT nodeid FROM '+@TableName+' WHERE father IN (SELECT nodeid FROM @TempTable1) AND nodeid NOT IN (SELECT nodeid FROM @TempTable1)
SELECT * FROM @TempTable1'exec (@sql)go
@Tablename varchar(40),
@nodeid int
as
declare @sql varchar(8000)set @sql='
DECLARE @TempTable1 TABLE (nodeid int,father int)
INSERT @TempTable1 select nodeid,father from '+@TableName+' where nodeid='+cast(@nodeid as varchar(10))+'
WHILE EXISTS (SELECT * FROM '+@TableName+' WHERE father IN (SELECT nodeid FROM @TempTable1) AND nodeid NOT IN (SELECT nodeid FROM @TempTable1))
INSERT @TempTable1 SELECT nodeid,father FROM '+@TableName+' WHERE father IN (SELECT nodeid FROM @TempTable1) AND nodeid NOT IN (SELECT nodeid FROM @TempTable1)
SELECT * FROM @TempTable1'exec (@sql)go
as
declare @test table( f_1 char(10))
declare @exec varchar(128)insert into @test values('test ')set @exec ='Select * from @test'exec (@exec)goexec p_test
go
drop procedure p_test
go
create procedure p_test
as
create table #test ( f_1 char(10))
declare @exec varchar(128)insert into #test values('test ')set @exec ='Select * from #test'exec (@exec)goexec p_test
godrop procedure p_test