SqlServer里边能否存储全局表变量?
实际问题是这样的,以下函数用于返回一个树目录所有子孙节点的字符串,但我现在希望放回一张
该目录所有子孙节点记录的记录集和他们的深度,请问如何做,我想应该是申请一个全局表变量
然后在遍历的时候往表变量里边插入记录,但是我不知道如何申请全局表变量
Create FUNCTION KMTreeClass(@InputId int,@IdStr varchar(8000)='')
RETURNS Varchar(8000)
AS
BEGIN
Declare @KM_CC int,@KM_UC int
DECLARE KM CURSOR local FOR
SELECT KM_CC,KM_UC
FROM KM
where KM_UC=@InputId
OPEN KM
FETCH NEXT FROM KM
INTO @KM_CC,@KM_UC
WHILE @@FETCH_STATUS = 0
BEGIN
if @IdStr<>'' select @IdStr=','+@IdStr
select @IdStr=''''+cast(@KM_CC as varchar)+''''+@IdStr
if @@NESTLEVEL<32
select @IdStr=dbo.KMTreeClass(@KM_CC,@IdStr)
else
set @IdStr=@IdStr+'['+cast(@KM_CC as varchar)+']'
-- select KMCC = 5,level1 = 6 into #KM
-- drop table #KM
FETCH NEXT FROM KM
INTO @KM_CC,@KM_UC
End
CLOSE KM
DEALLOCATE KM
Return @IdStr
END