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