代码
1 ----流水号配置表-----
2 CREATE TABLE [dbo].[shg](
3 [tableName] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
4 [preLetter] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
5 [placeLength] [int] NULL,
6 [beginNum] [int] NULL,
7 [endNum] [int] NULL,
8 [currentNum] [int] NULL,
9 [regTime] [datetime] NULL,
10 [Step] [smallint] NULL,
11 [Description] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
12 CONSTRAINT [PK_shg] PRIMARY KEY CLUSTERED
13 (
14 [tableName] ASC
15 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
16 ) ON [PRIMARY]
17
18 ---流水号生成器---
19
20 set ANSI_NULLS ON
21 set QUOTED_IDENTIFIER ON
22 go
23
24 -- =============================================
25 -- Author: <anncesky>
26 -- Create date: <2010/3/16>
27 -- Description: <生成流水号>
28 -- =============================================
29 CREATE PROCEDURE [dbo].[sp_AutoSht]
30 @tableName varchar(50), --表名
31 @isAdd bit = 0, --运行存储过程后是否自增1:是 0:否
32 @result varchar(50) output--传出的流水号
33 AS
34 declare @sqlTemp nvarchar(100),
35 @preLetter varchar(50),@placeLength int,@beginNum int,@endNum int,@currentNum int,@Step int
36 BEGIN
37 SET NOCOUNT ON;
38 if EXISTS (select tableName from shg where tableName=@tableName)
39 begin
40 select @preLetter=preLetter,@placeLength=placeLength,@beginNum=beginNum,@endNum=endNum,
41 @currentNum=currentNum,@Step=Step from shg where tableName=@tableName
42 end
43 else
44 begin
45 --return 0
46 set @preLetter='Null'
47 set @placeLength=6
48 set @beginNum=1
49 set @endNum=999999
50 set @currentNum=1
51 set @Step=1
52 insert into shg(tableName,preLetter,placeLength,beginNum,endNum,currentNum,Step,regTime)
53 values(@tableName,@preLetter,@placeLength,@beginNum,@endNum,@currentNum,@Step,getdate())
54 end
55 if @isAdd=1
56 begin
57 if len(@currentNum+@Step)<=@placeLength
58 update shg set currentNum=currentNum+@Step where tableName=@tableName
59 else
60 begin
61 --RAISERROR('超出原定范围',16,1)
62 update shg set placeLength=placeLength+1,currentNum=currentNum+@Step where tableName=@tableName
63 end
64 end
65 set @result=@preLetter+replicate('0',@placeLength-len(@currentNum))+cast(@currentNum as varchar)
66
67 return 1
68 END
2 CREATE TABLE [dbo].[shg](
3 [tableName] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
4 [preLetter] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
5 [placeLength] [int] NULL,
6 [beginNum] [int] NULL,
7 [endNum] [int] NULL,
8 [currentNum] [int] NULL,
9 [regTime] [datetime] NULL,
10 [Step] [smallint] NULL,
11 [Description] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
12 CONSTRAINT [PK_shg] PRIMARY KEY CLUSTERED
13 (
14 [tableName] ASC
15 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
16 ) ON [PRIMARY]
17
18 ---流水号生成器---
19
20 set ANSI_NULLS ON
21 set QUOTED_IDENTIFIER ON
22 go
23
24 -- =============================================
25 -- Author: <anncesky>
26 -- Create date: <2010/3/16>
27 -- Description: <生成流水号>
28 -- =============================================
29 CREATE PROCEDURE [dbo].[sp_AutoSht]
30 @tableName varchar(50), --表名
31 @isAdd bit = 0, --运行存储过程后是否自增1:是 0:否
32 @result varchar(50) output--传出的流水号
33 AS
34 declare @sqlTemp nvarchar(100),
35 @preLetter varchar(50),@placeLength int,@beginNum int,@endNum int,@currentNum int,@Step int
36 BEGIN
37 SET NOCOUNT ON;
38 if EXISTS (select tableName from shg where tableName=@tableName)
39 begin
40 select @preLetter=preLetter,@placeLength=placeLength,@beginNum=beginNum,@endNum=endNum,
41 @currentNum=currentNum,@Step=Step from shg where tableName=@tableName
42 end
43 else
44 begin
45 --return 0
46 set @preLetter='Null'
47 set @placeLength=6
48 set @beginNum=1
49 set @endNum=999999
50 set @currentNum=1
51 set @Step=1
52 insert into shg(tableName,preLetter,placeLength,beginNum,endNum,currentNum,Step,regTime)
53 values(@tableName,@preLetter,@placeLength,@beginNum,@endNum,@currentNum,@Step,getdate())
54 end
55 if @isAdd=1
56 begin
57 if len(@currentNum+@Step)<=@placeLength
58 update shg set currentNum=currentNum+@Step where tableName=@tableName
59 else
60 begin
61 --RAISERROR('超出原定范围',16,1)
62 update shg set placeLength=placeLength+1,currentNum=currentNum+@Step where tableName=@tableName
63 end
64 end
65 set @result=@preLetter+replicate('0',@placeLength-len(@currentNum))+cast(@currentNum as varchar)
66
67 return 1
68 END
----测试----
declare @r varchar(50)
--set @r='ddd'
exec sp_AutoSht 'aaa',1,@r output
print @r
declare @r varchar(50)
--set @r='ddd'
exec sp_AutoSht 'aaa',1,@r output
print @r
玩技术,要学会忍受寂寞--