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

利用储存过程实现

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