--====================================/=======================================
--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字段
@ParentFie
-
-
-
-
-
- create proc [dbo].[cmsdream_SP_Navigate](
- @Type varchar(20),
- @TableName varchar(50),
- @PrimaryField varchar(50),
- @ParentField varchar(50),
- @CurrentID int,
- @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'
- */
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步