真是林子大了,什么鸟都有。客户的需求也是千变万化。
前阵子就因为一个客户的要求,需要把原本手工填写的东西改成自动生成,因为他们填的信息不符合标准。
真是林子大了,什么鸟都有。客户的需求也是千变万化。
前阵子就因为一个客户的要求,需要把原本手工填写的东西改成自动生成,因为他们填的信息不符合标准。(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+'
'
![](/Images/OutliningIndicators/None.gif)
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
![](/Images/OutliningIndicators/None.gif)
GO
![](/Images/OutliningIndicators/None.gif)
调用示例:
exec sp_UpdateNumber '','','departnumber','parentdepart','departid','department'