[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]