数据表结构:
tdate smalldatetime 4
sno int 4
1
--
功能:获取序列号
2 -- ret: 0—OK, -1—超过9999
3 CREATE PROCEDURE ap_GetSerialCode
4
5 @sCode char ( 20 ) output
6 AS
7 declare
8 @tdate varchar ( 10 ),
9 @today varchar ( 10 ),
10 @sno int
11 select top 1 @tdate = Convert ( varchar ( 10 ), tdate, 120 ),
12 @today = Convert ( varchar ( 10 ), getdate (), 120 ),
13 @sno = sno
14 from tblno
15 if @tdate = @today
16 begin
17 set @sno = @sno + 1
18 if @sno > 9999
19 goto err
20 update tblno
21 set sno = @sno
22 end
23 else
24 begin
25 update tblno
26 set tdate = @today , sno = 1
27 set @sno = 1
28 end
29
30 set @tdate = Convert ( varchar ( 6 ), Convert ( smalldatetime , @today ), 12 )
31 set @sCode = @tdate + right ( Convert ( varchar ( 5 ), @sno + 10000 ), 4 )
32 return 0
33 err:
34 return - 1
35 GO
36
2 -- ret: 0—OK, -1—超过9999
3 CREATE PROCEDURE ap_GetSerialCode
4
5 @sCode char ( 20 ) output
6 AS
7 declare
8 @tdate varchar ( 10 ),
9 @today varchar ( 10 ),
10 @sno int
11 select top 1 @tdate = Convert ( varchar ( 10 ), tdate, 120 ),
12 @today = Convert ( varchar ( 10 ), getdate (), 120 ),
13 @sno = sno
14 from tblno
15 if @tdate = @today
16 begin
17 set @sno = @sno + 1
18 if @sno > 9999
19 goto err
20 update tblno
21 set sno = @sno
22 end
23 else
24 begin
25 update tblno
26 set tdate = @today , sno = 1
27 set @sno = 1
28 end
29
30 set @tdate = Convert ( varchar ( 6 ), Convert ( smalldatetime , @today ), 12 )
31 set @sCode = @tdate + right ( Convert ( varchar ( 5 ), @sno + 10000 ), 4 )
32 return 0
33 err:
34 return - 1
35 GO
36
PS:貌似这个还不是很完善,需要在研究下。
注转载请注明出处,来自demonlion
By--demonlion