MSSQL存储过程也玩“递归”

真是林子大了，什么鸟都有。客户的需求也是千变万化。
前阵子就因为一个客户的要求，需要把原本手工填写的东西改成自动生成，因为他们填的信息不符合标准。（ps:看来不应该要求客户输入太多的东西，尽可能做到客户少敲点键盘）

我们系统有一个表如下：

create   table  department(
    departid  int   identity ( 1 , 1 )  primary   key ,  -- 部门标识
    departnumber  nvarchar ( 20 )  not   null ,         -- 部门编码
    departname  nvarchar ( 50 )  not   null ,         -- 部门名称
    parentdepart  nvarchar ( 50 )                 -- 上级部门
)

其中 parentdepart 根据 departid 生成，顶级为空例如
departid    parentdepart
1              
2              1_
3              1_
4              1_3_
5              1_2_
6              1_2_5_
...             ....
基本的关系相信大家应该很清楚了，系统默认departnumber是客户自己输入的，所以各种各样的数据都有，后来数据太多了，有很多地方不便或出错。我们不得不给他们改成诸如下面的格式:

01
0101
02
0201
020101
020102
...
然后让程序自动生成，这时候麻烦来了，数据库中已经有几百条数据了，总不成一个一个手工给他们改吧。最开始想到写一个页面，搞一点简单的代码然后运行一下就全部OK了。
但后来在很多项目都发现这种现象，难道又写一点代码呀？看来得想个一劳永逸的方法才行。
......
经过摸过，终于用存储过程递归搞定。
下面是相应的存储过程，其中用到了游标等.（有很多人可能不喜欢用游标）

-- 根据相应关系更新编号
create   procedure  sp_UpdateNumber(
     @ParentNumber   nvarchar ( 100 ) = '' ,         -- 上级编号
     @ParentRelation   nvarchar ( 1000 ) = '' ,     -- 和上级的关系
     @NumberField   nvarchar ( 20 ),             -- 编号字段
     @RelationField   nvarchar ( 20 ),         -- 层次关系字段
     @FieldName   nvarchar ( 20 ),             -- 生成层次关系字段
     @TableName   nvarchar ( 20 )                 -- 表名称
)
as
     declare   @sqlstr   nvarchar ( 4000 )
    
     set   @sqlstr = '
        declare @count int
        set @count=1
    
        declare @coding nvarchar(100)
    
        declare cur_0 ' + @ParentNumber + '  cursor for select  ' + @FieldName + '  from  ' + @TableName + '  where  ' + @RelationField + ' =@ParentRelation
        open cur_0 ' + @ParentNumber + '
    
        declare @number int
        declare @relation nvarchar(200)
    
        fetch next from cur_0 ' + @ParentNumber + '  into @number
        while @@fetch_status=0
            begin
                set @coding=@ParentNumber+replicate( '' 0 '' ,2-len(@count))+convert(nvarchar,@count)
                update  ' + @TableName + '  set  ' + @NumberField + ' =@coding where  ' + @FieldName + ' =@number
                
                set @relation=@ParentRelation+convert(nvarchar,@number)+ '' _ ''
    
                exec sp_UpdateNumber @coding,@relation,@NumberField,@RelationField,@FieldName,@TableName
    
                set @count=@count+1
                
                fetch next from cur_0 ' + @ParentNumber + '  into @number
            end
    
        close cur_0 ' + @ParentNumber + '
        deallocate cur_0 ' + @ParentNumber + '
     '

     exec  sp_executesql  @sqlstr ,
        N ' @ParentNumber nvarchar(100),@ParentRelation nvarchar(1000),@NumberField nvarchar(20),@RelationField nvarchar(20),@FieldName nvarchar(20),@TableName nvarchar(20) ' ,
         @ParentNumber , @ParentRelation , @NumberField , @RelationField , @FieldName , @TableName

GO

调用示例：

exec  sp_UpdateNumber  '' , '' , ' departnumber ' , ' parentdepart ' , ' departid ' , ' department '

转载于:https://www.cnblogs.com/lyout/archive/2007/05/17/750274.html