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

--====================================/=======================================
--Powered By CMSDream Copyright © 2007-2008 All rights reserved.
--13:32 2008-12-26
--通用获取父节点/子节点/子节点下所有节点ID的存储过程
--====================================/=======================================
create proc [dbo].[cmsdream_SP_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'
*/