1、表结构和效果图,这个表是用来存储基础因子的,需要的可以拓展字段,比如,升序,降序,起始序号等。
CREATE
TABLE
[
dbo
]
.
[
SerialNo
]
(
[ sCode ] [ varchar ] ( 50 ) NOT NULL , -- 主键也是多个流水号的类别区分
[ sName ] [ varchar ] ( 100 ) NULL , -- 名称,备注形式
[ sQZ ] [ varchar ] ( 50 ) NULL , -- 前缀
[ sValue ] [ varchar ] ( 80 ) NULL , -- 因子字段
CONSTRAINT [ PK_SerialNo ] PRIMARY KEY CLUSTERED
(
[ sCode ] ASC
) WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON ,
ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ]
) ON [ PRIMARY ]
[ sCode ] [ varchar ] ( 50 ) NOT NULL , -- 主键也是多个流水号的类别区分
[ sName ] [ varchar ] ( 100 ) NULL , -- 名称,备注形式
[ sQZ ] [ varchar ] ( 50 ) NULL , -- 前缀
[ sValue ] [ varchar ] ( 80 ) NULL , -- 因子字段
CONSTRAINT [ PK_SerialNo ] PRIMARY KEY CLUSTERED
(
[ sCode ] ASC
) WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON ,
ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ]
) ON [ PRIMARY ]
2、存储过程代码
1
Create
procedure
[
dbo
]
.
[
GetSerialNo
]
2 (
3 @sCode varchar ( 50 )
4 )
5
6 as
7
8 -- exec GetSerialNo
9
10 begin
11
12 Declare @sValue varchar ( 16 ),
13
14 @dToday datetime ,
15
16 @sQZ varchar ( 50 ) -- 这个代表前缀
17
18 Begin Tran
19
20 Begin Try
21
22 -- 锁定该条记录,好多人用lock去锁,起始这里只要执行一句update就可以了
23 -- 在同一个事物中,执行了update语句之后就会启动锁
24 Update SerialNo set sValue = sValue where sCode = @sCode
25
26 Select @sValue = sValue From SerialNo where sCode = @sCode
27
28 Select @sQZ = sQZ From SerialNo where sCode = @sCode
29
30 -- 因子表中没有记录,插入初始值
31
32 If @sValue is null
33
34 Begin
35
36 Select @sValue = convert ( bigint , convert ( varchar ( 6 ), getdate (), 12 ) + ' 000001 ' )
37
38 Update SerialNo set sValue = @sValue where sCode = @sCode
39
40 end else
41
42 Begin -- 因子表中没有记录
43
44 Select @dToday = substring ( @sValue , 1 , 6 )
45
46 -- 如果日期相等,则加1
47
48 If @dToday = convert ( varchar ( 6 ), getdate (), 12 )
49
50 Select @sValue = convert ( varchar ( 16 ), ( convert ( bigint , @sValue ) + 1 ))
51
52 else -- 如果日期不相等,则先赋值日期,流水号从1开始
53
54 Select @sValue = convert ( bigint , convert ( varchar ( 6 ), getdate (), 12 ) + ' 000001 ' )
55
56
57
58 Update SerialNo set sValue = @sValue where sCode = @sCode
59
60 End
61
62 Select result = @sQZ + @sValue
63
64 Commit Tran
65
66 End Try
67
68 Begin Catch
69
70 Rollback Tran
71
72 Select result = ' Error '
73
74 End Catch
75
76 end
77
78
2 (
3 @sCode varchar ( 50 )
4 )
5
6 as
7
8 -- exec GetSerialNo
9
10 begin
11
12 Declare @sValue varchar ( 16 ),
13
14 @dToday datetime ,
15
16 @sQZ varchar ( 50 ) -- 这个代表前缀
17
18 Begin Tran
19
20 Begin Try
21
22 -- 锁定该条记录,好多人用lock去锁,起始这里只要执行一句update就可以了
23 -- 在同一个事物中,执行了update语句之后就会启动锁
24 Update SerialNo set sValue = sValue where sCode = @sCode
25
26 Select @sValue = sValue From SerialNo where sCode = @sCode
27
28 Select @sQZ = sQZ From SerialNo where sCode = @sCode
29
30 -- 因子表中没有记录,插入初始值
31
32 If @sValue is null
33
34 Begin
35
36 Select @sValue = convert ( bigint , convert ( varchar ( 6 ), getdate (), 12 ) + ' 000001 ' )
37
38 Update SerialNo set sValue = @sValue where sCode = @sCode
39
40 end else
41
42 Begin -- 因子表中没有记录
43
44 Select @dToday = substring ( @sValue , 1 , 6 )
45
46 -- 如果日期相等,则加1
47
48 If @dToday = convert ( varchar ( 6 ), getdate (), 12 )
49
50 Select @sValue = convert ( varchar ( 16 ), ( convert ( bigint , @sValue ) + 1 ))
51
52 else -- 如果日期不相等,则先赋值日期,流水号从1开始
53
54 Select @sValue = convert ( bigint , convert ( varchar ( 6 ), getdate (), 12 ) + ' 000001 ' )
55
56
57
58 Update SerialNo set sValue = @sValue where sCode = @sCode
59
60 End
61
62 Select result = @sQZ + @sValue
63
64 Commit Tran
65
66 End Try
67
68 Begin Catch
69
70 Rollback Tran
71
72 Select result = ' Error '
73
74 End Catch
75
76 end
77
78
请注明出处[小虎原创]:http://www.52rs.net/ArticleView.aspx?gID=71bd9b1d-ad30-4f6e-896d-fed7dfbc1b3d