( 一 )
这个方式比较简单,
在Storeprocedure的参数中给出
TableName;字段名称;规则; 通过返回值得到输出.
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Sql Procedure
1
set ANSI_NULLS ON
2
set QUOTED_IDENTIFIER ON
3
go
4![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
create Procedure [dbo].[Up_GetNewID]
6
@strTName varchar(50), --Table name
7
@strFName varchar(50), -- Column Name
8
@strID varchar(20), -- ID
9
@intIDBit int, --4
10
@strSerialCode varchar(1), --
11
@strYM varchar(6),
12
@strPreFixID varchar(10),
13
@strNewID varchar(20) = '' Output
14![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
AS
16
Begin
17
Declare @strTempID varchar(20)
18
Declare @strZero varchar(20)
19
Declare @strTempYM varchar(6)
20
Declare @strTempSCode varchar(20)
21
Declare @Query varchar(8000)
22
23![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
Set @strZero=Replicate('0',@intIDBit)
26![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
If @strID<>''
28
Set @strNewID=@strPreFixID + Left(@strID,Len(@strID) - (Len(@strSerialCode) + @intIDBit)) +
29
@strSerialCode +
30
Right(@strZero + Cast(Cast(Right(@strID,@intIDBit) As int) + 1 As varchar),@intIDBit)
31
32
Else
33
Begin
34
If @strYM=''
35
Set @strTempYM = Cast(Year(GetDate()) As varchar) + Right('0' + Cast(Month(GetDate()) As varchar),2)
36
Else
37
Set @strTempYM=@strYM
38![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
39
If @strSerialCode=''
40
Set @strTempSCode=''
41
Else
42
Set @strTempSCode='+ Cast(' + @strSerialCode + ' As varchar(1)) '
43
44
Set @Query = 'Select @strTempID = ' +
45
' Top 1 ' + @strFName + ' ' +
46
'From ' + @strTName + ' ' +
47
'Where Left(' + @strFName + ',len(' + @strFName + ')-' + Cast(@intIDBit As varchar) + ') =''' +
48
@strPreFixID + @strTempYM + '''' + @strTempSCode + ' ' +
49
'Order By ' + @strFName + ' Desc '
50
51
52
Exec(@Query)
53
54![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
55
If (@strTempID = '' Or @strTempID Is Null)
56
Set @strNewID = @strPreFixID + @strTempYM +
57
@strSerialCode + Left(@strZero, Len(@strZero) - 1) + '1'
58
Else
59![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
60
Set @strNewID = Left(@strTempID, Len(@strTempID) - (Len(@strSerialCode) + @intIDBit)) +
61
@strSerialCode +
62
Cast(Right(@strZero + Cast(Cast(Right(@strTempID,@intIDBit) As int) + 1 As varchar),@intIDBit) As varchar)
63
End
64
End
65![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
66![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
67![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
Sample:
new table :
CREATE TABLE [dbo].[Leaves](
[DocID] [nvarchar](200) NOT NULL,
CONSTRAINT [Leaves_PK] PRIMARY KEY CLUSTERED
(
[DocID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
//得到iD
declare @strNewID varchar(20)
exec [GetNewID] 'dbo.Leaves' , 'DocID' , '', 4 ,'' , '','L', @strNewID output
select @strNewID
大家可以试一下。
当然上面的这个格式也仅仅支持
{*}yyyymm####{几位} 的方式.
( 二 )
后面继续我们写一个更多功能的编号自动生成