/*
zqonline
2009-04-21
获取提定ID的路径
调有方法:
declare @path nvarchar(4000)
exec p_Common_TreePath 'tb_dir','id','parentid','\',8,@path output
select @path
*/
CREATE PROCEDURE p_Common_TreePath
@tablename nvarchar ( 200 ) -- 数据表名称
, @IDName nvarchar ( 100 ) -- ID字段名称
, @ParentIDName nvarchar ( 100 ) -- ParentID字段名称
, @PathSeparator nvarchar ( 10 ) -- 路径分隔符
, @IDValue nvarchar ( 100 ) -- 待查询的ID值
, @Path nvarchar ( 4000 ) output -- 输出父路径
AS
declare @sql nvarchar ( 4000 )
set @path = ''
set @sql = '
declare @id nvarchar(100)
declare @parentID nvarchar(100)
set @parentID= ''' + @IDValue + '''
while 1=1
begin
select @id= ' + @IDName + ' ,@parentID= ' + @ParentIDName + ' from ' + @tablename + ' where ' + @IDName + ' =@parentID
if @@rowcount=0
begin
break
end
if len(@path)>0
begin
set @path= ''' + @PathSeparator + ''' +@path
end
set @path=convert(varchar,@id)+@path
end
'
declare @parPath nvarchar ( 4000 )
set @parPath = ' @path nvarchar(4000) output '
execute sp_executesql @sql , @parPath , @path = @path output
set @path = isnull ( @path , '' )
return 0
GO
zqonline
2009-04-21
获取提定ID的路径
调有方法:
declare @path nvarchar(4000)
exec p_Common_TreePath 'tb_dir','id','parentid','\',8,@path output
select @path
*/
CREATE PROCEDURE p_Common_TreePath
@tablename nvarchar ( 200 ) -- 数据表名称
, @IDName nvarchar ( 100 ) -- ID字段名称
, @ParentIDName nvarchar ( 100 ) -- ParentID字段名称
, @PathSeparator nvarchar ( 10 ) -- 路径分隔符
, @IDValue nvarchar ( 100 ) -- 待查询的ID值
, @Path nvarchar ( 4000 ) output -- 输出父路径
AS
declare @sql nvarchar ( 4000 )
set @path = ''
set @sql = '
declare @id nvarchar(100)
declare @parentID nvarchar(100)
set @parentID= ''' + @IDValue + '''
while 1=1
begin
select @id= ' + @IDName + ' ,@parentID= ' + @ParentIDName + ' from ' + @tablename + ' where ' + @IDName + ' =@parentID
if @@rowcount=0
begin
break
end
if len(@path)>0
begin
set @path= ''' + @PathSeparator + ''' +@path
end
set @path=convert(varchar,@id)+@path
end
'
declare @parPath nvarchar ( 4000 )
set @parPath = ' @path nvarchar(4000) output '
execute sp_executesql @sql , @parPath , @path = @path output
set @path = isnull ( @path , '' )
return 0
GO