SQL2005动态表无限级分类存储过程

  • O4_864461
    了解作者
  • 6.7KB
    文件大小
  • rar
    文件格式
  • 0
    收藏次数
  • VIP专享
    资源类型
  • 0
    下载次数
  • 2022-04-28 02:43
    上传日期
这个是修改其它作者单一无限级分类存储过程版,修改后为动态表无限级分类存储过程,共享给大家. 适合于sql 2005
SQL2005Column.rar
  • sp_Column_Insert.sql
    8.8KB
  • sp_Column_Delete.sql
    5KB
  • sp_Column_Update.sql
    18.7KB
  • sp_Column_List.sql
    2.4KB
内容介绍
USE [HuaSheng] GO /****** 对象: StoredProcedure [dbo].[sp_Column_Update] 脚本日期: 03/04/2008 10:16:14 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <何剑伟> -- Create date: <2007-12-27> -- Description: <无限级分类--增加项> -- OICQ:6102341 -- ============================================= -- 表名:Tb_Column -- 表结构(所有字段非空): -- Column_ID int 主键(注:非标识) -- Column_Name nvarchar(50)分类名称 -- Parent_ID int 父分类ID(默认值0) -- Column_Path nvarchar(1000) 分类路径 -- Column_Depth int分类深度(默认值0) -- Column_Order int排序(默认值0) -- Column_Intro nvarchar(1000)分类说明 -- ============================================= -- 测试:exec dbo.sp_Column_Delete 'ColumnA',3 -- ============================================= CREATE PROCEDURE [dbo].[sp_Column_Update] @TableName nvarchar(50), @Column_ID int, @Parent_ID int, @Column_Name nvarchar(50), @Column_Intro nvarchar(1000), @Column_TempString nvarchar(2000)=NULL --补充字段集 AS Declare @Err int Set @Err=0 BEGIN Tran --获取修改前的:Parent_ID,Column_Depth,Column_Order Declare @strTmpA nvarchar(1000), --临时Sql @oParent_ID int, @oColumn_Depth int, @oColumn_Order int, @oColumn_Path nvarchar(1000) SET @strTmpA=' SELECT @oParent_ID=Parent_ID, @oColumn_Depth='+@TableName+'_Depth,@oColumn_Order='+@TableName+'_Order, @oColumn_Path='+@TableName+'_Path FROM '+@TableName+' WHERE '+@TableName+'_ID = '+STR(@Column_ID)+' ' EXEC sp_executesql @strTmpA,N'@oParent_ID int out,@oColumn_Depth int out,@oColumn_Order int out,@oColumn_Path nvarchar(1000) out ',@oParent_ID out,@oColumn_Depth out,@oColumn_Order out,@oColumn_Path out IF @oParent_ID Is Null Begin Set @Err = 1 Goto theEnd End --如果父ID没有改变,则直接修改栏目名和栏目简介 IF @oParent_ID = @Parent_ID Begin SET @strTmpA=' UPDATE '+@TableName+' SET '+@TableName+'_Name = '+@Column_Name+','+@TableName+'_Intro = '+@Column_Intro+' WHERE '+@TableName+'_ID = '+STR(@Column_ID)+' ' EXEC sp_executesql @strTmpA IF @@Error <> 0 Set @Err = 2 Goto theEnd End Declare @nColumn_Path nvarchar(1000), @nColumn_Depth int, @nColumn_Order int --获取当前节点作为父节点所包含的节点数[包括自身] 注:如果返回 “1” 说明是单节点 Declare @theCount int SET @strTmpA=' SELECT @theCount = Count('+@TableName+'_ID) FROM '+@TableName+' WHERE '+@TableName+'_ID='+@Column_ID+' Or '+@TableName+'_Path like '''+@oColumn_Path+'|%'' ' EXEC sp_executesql @strTmpA,N'@theCount int out',@theCount OUT IF @theCount Is Null Begin Set @Err = 3 Goto theEnd End IF @Parent_ID=0 --如果是设置为顶级节点,将节点设置为最后一个顶级节点 Begin --Print '设置为顶级栏目' Set @nColumn_Path = Ltrim(Str(@Column_ID)) Set @nColumn_Depth =1 SET @strTmpA=' SELECT @nColumn_Order = Max('+@TableName+'_Order) FROM '+@TableName+' ' EXEC sp_executesql @strTmpA,N'@nColumn_Order int out',@nColumn_Order OUT IF @nColumn_Order Is NULL Begin Set @Err = 4 Goto theEnd End Set @nColumn_Order = @nColumn_Order - @theCount + 1 --更新三部分 1 节点本身 2 所有子节点 2 本树更改之前的后面记录的顺序 --Print '更新本栏目之前位置后面的所有栏目[不包括本栏目下的子栏目]的:Column_Order' SET @strTmpA=' UPDATE '+@TableName+' SET '+@TableName+'_Order = '+@TableName+'_Order-'+STR(@theCount)+' WHERE ('+@TableName+'_Order >'+STR(@oColumn_Order)+') And ('+@TableName+'_Path Not like '''+@oColumn_Path+'|%'') ' EXEC sp_executesql @strTmpA IF @@Error <> 0 Begin Set @Err = 7 Goto theEnd End --Print '更新本栏目的:Parent_ID,Column_Path,Column_Depth,Column_Order,Column_Name,Column_Intro' --Print 'Order : '+Ltrim(Str(@nColumn_Order)) SET @strTmpA=' UPDATE '+@TableName+' SET '+@TableName+'_ID='+STR(@Parent_ID)+', '+@TableName+'_Path='+@nColumn_Path+', '+@TableName+'_Depth='+STR(@nColumn_Depth)+', '+@TableName+'_Order='+STR(@nColumn_Order)+', '+@TableName+'_Name='+@Column_Name+', '+@TableName+'_Intro='+@Column_Intro+' WHERE '+@TableName+'�
评论
    相关推荐