数据库设计：系统编码规则的自定义
SQLServer的实现
本文的思路是依据不同的设置得到不同的单据号，全数据库存储过程实现！
Create Table tCore_CodeRule
(
CodeRuleID varchar(80) not null,
Head varchar(6) null,
MiddleRule varchar(8) null,
Length int not null default 4,
Tail varchar(8) null,
Describe varchar(200) null,
RecordDay varchar(10) null,
Record int null,
State bit not null default '1',
Constraint tCore_CodeRule_PK Primary Key(CodeRuleID)
)
go
Create Procedure pGet_CodeRule
@@CodeRuleID varchar(80), @@ResultCode varchar(40) output
--WITH ENCRYPTION
as
begin
if((select count(*) from tCore_CodeRule where CodeRuleID = @@CodeRuleID) =0)
begin
print 'Not exists [' + @@CodeRuleID+']'
select @@ResultCode = ''
return
end
declare @Head varchar(6)
declare @MiddleRule varchar(8)
declare @Length int
declare @Tail varchar(8)
declare @RecordDay varchar(10)
declare @Record int
declare @State bit
select @Head = Head,@MiddleRule = MiddleRule,@Length = Length, @Tail = Tail, @RecordDay = RecordDay, @Record = Record, @State = State
from tCore_CodeRule
where CodeRuleID = @@CodeRuleID
if (@State = '0')
begin
print 'State == 0 [' + @@CodeRuleID+']'
select @@ResultCode = ''
return
end
select @@ResultCode = @Head
If (@MiddleRule ='YYYYMMDD')
begin
select @MiddleRule = convert(varchar(8),Getdate(),112)
end
else if (@MiddleRule = 'YYMMDD')
Begin
select @MiddleRule = substring(convert(varchar(8),Getdate(),112),3,8)
end
else if (@MiddleRule = 'MMDD')
Begin
select @MiddleRule = substring(convert(varchar(8),Getdate(),112),5,8)
end
else
Begin
select @MiddleRule = isnull(@MiddleRule,'')
End
select @@ResultCode = @@ResultCode + @MiddleRule
if (@RecordDay = @MiddleRule)
begin
select @Record = @Record + 1
Update tCore_CodeRule
set Record = @Record
where CodeRuleID = @@CodeRuleID
end
else if (@RecordDay is not null)
begin
select @Record = 1
Update tCore_CodeRule
set Record = @Record, RecordDay = @MiddleRule
where CodeRuleID = @@CodeRuleID
end
else
begin
select @Record = @Record + 1
Update tCore_CodeRule
set Record = @Record
where CodeRuleID = @@CodeRuleID
end
select @@ResultCode = @@ResultCode + Replicate('0',@Length - Len(convert(varchar(10),@Record))) + convert(varchar(10),@Record)
If (@Tail ='YYYYMMDD')
begin
select @@ResultCode = @@ResultCode + convert(varchar(8),Getdate(),112)
end
else if (@Tail = 'YYMMDD')
Begin
select @@ResultCode = @@ResultCode + substring(convert(varchar(8),Getdate(),112),3,8)
end
else if (@Tail = 'MMDD')
Begin
select @@ResultCode = @@ResultCode + substring(convert(varchar(8),Getdate(),112),5,8)
end
else
Begin
select @Tail = isnull(@Tail,'')
select @@ResultCode = @@ResultCode + @Tail
End
return
end
go
应用举例：
表记录：
执行前：
CodeRuleID Head MiddleRule Length Tail RecordDay Record State
BookMark.RecordID B YYYYMMDD 6 20051124 2 1
Document.RecordID D YYYYMMDD 6 20051025 1 1
执行取值：
Exec pGet_CodeRule 'BookMark.RecordID',''
得到：
Result: B20060125000001
改变表记录：
CodeRuleID Head MiddleRule Length Tail RecordDay Record State
BookMark.RecordID B YYYYMMDD 6 20050125 1 1
Document.RecordID D YYYYMMDD 6 20051025 1 1
呵呵 以上的一点小技巧希望能够对大家有所启发：）
