通用获取父节点/子节点/子节点下所有节点ID的存储过程

[code=SQL]

create proc [dbo].[Local_Navigate](  
     @Type varchar(20),      -- parent/sub/all  
     @TableName varchar(50),     --表名  
     @PrimaryField varchar(50),  --数据表的主ID字段  
     @ParentField varchar(50),   --数据表中的父ID字段  
     @CurrentID int,         --表中当前主ID  
     @OutputField varchar(1000) = '',  
     @OrderField varchar(50) = '' 
 )AS 
 begin 
     if @CurrentID <= 0 return 
     set @Type = lower(@Type)  
     if @OutputField = '' set @OutputField = '*' 
     declare @sql nvarchar(4000)  
     declare @IDList nvarchar(2000)  
  
     if @Type = 'all' 
     begin 
         set @IDList = cast(@CurrentID As nvarchar(12))  
  
         declare @IDTemp1 nvarchar(2000) set @IDTemp1 = @IDList  
         declare @IDTemp2 nvarchar(2000) set @IDTemp2 = '' 
         declare @SubCount int set @SubCount = 1  
  
         while @SubCount > 0  
         begin 
             set @IDTemp2 = '' 
             if len(@IDTemp1) > 0  
             begin 
                 set @sql = 'select @IDTemp2 = @IDTemp2 + '','' + cast([' + @PrimaryField + '] As nvarchar(12)) from ' + @TableName + ' where [' + @ParentField + '] IN (' + @IDTemp1 + ')' 
                 exec sp_executesql @sql,N'@IDTemp2 nvarchar(2000) output',@IDTemp2 output 
             end 
  
             if len(@IDTemp2) > 1  
             begin 
                 set @IDTemp2 = substring(@IDTemp2,2,len(@IDTemp2)-1)  
                 set @IDList = @IDList + ',' + @IDTemp2  
             end 
             set @IDTemp1 = @IDTemp2  
  
             set @SubCount = 0  
             if len(@IDTemp2) > 1  
             begin 
                 set @sql = 'select @SubCount = count(*) from ' + @TableName + ' where [' + @ParentField + '] IN (0' + @IDTemp2 + ')' 
                 exec sp_executesql @sql,N'@SubCount int output',@SubCount output 
             end 
         end 
         if @OrderField = '' 
             exec('select ' + @OutputField + ' from ' + @TableName + ' where [' + @PrimaryField + '] IN (' + @IDList + ')')  
         else 
             exec('select ' + @OutputField + ' from ' + @TableName + ' where [' + @PrimaryField + '] IN (' + @IDList + ') Order BY ' + @OrderField)  
     end 
  
     if @Type = 'parent' 
     begin 
         set @IDList = cast(@CurrentID As nvarchar(12)) + ',' 
         declare @ParentID int set @ParentID = 0  
           
         set @sql = 'select @ParentID = [' + @ParentField + '] from ' + @TableName + ' where [' + @PrimaryField + '] = ' + cast(@CurrentID As nvarchar(12))  
         exec sp_executesql @sql,N'@ParentID int output',@ParentID output 
  
         while @ParentID > 0  
         begin 
             set @IDList = @IDList + cast(@ParentID As nvarchar(12)) + ',' 
             set @sql = 'select @ParentID = [' + @ParentField + '] from ' + @TableName + ' where [' + @PrimaryField + '] = ' + cast(@ParentID As nvarchar(12))  
             exec sp_executesql @sql,N'@ParentID int output',@ParentID output 
         end 
         set @IDList = substring(@IDList,1,len(@IDList)-1)  
         if @OrderField = '' 
             exec('select ' + @OutputField + ' from ' + @TableName + ' where [' + @PrimaryField + '] IN (' + @IDList + ')')  
         else 
             exec('select ' + @OutputField + ' from ' + @TableName + ' where [' + @PrimaryField + '] IN (' + @IDList + ') Order BY ' + @OrderField)  
     end 
  
           
     if @Type = 'sub' 
     begin 
         if @OrderField = '' 
             exec('select ' + @OutputField + ' from ' + @TableName + ' where [' + @ParentField + '] = ' + @CurrentID + ' OR [' + @PrimaryField + '] = ' + @CurrentID)  
         else 
             exec('select ' + @OutputField + ' from ' + @TableName + ' where [' + @ParentField + '] = ' + @CurrentID + ' OR [' + @PrimaryField + '] = ' + @CurrentID + ' Order BY ' + @OrderField)  
     end 
 end
 /*  
     --测试  
     exec cmsdream_SP_Navigate 'parent','cmsdream_Nodes','NodeID','ParentID',116,'NodeID,Name','NavSort Desc' 所有父节点
     exec cmsdream_SP_Navigate 'sub','cmsdream_Nodes','NodeID','ParentID',76,'NodeID,Name'  当前子节点
     exec cmsdream_SP_Navigate 'all','cmsdream_Nodes','NodeID','ParentID',4,'NodeID,Name'  所有子节点
 */  
GO

[/code]