Database Design: custom system encoding rules

Custom system encoding rules

SQLServer implementation
The idea of this article is to get different document numbers based on different settings, and implement the full database stored procedure!

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)
End
Else if (@ MiddleRule = 'mmdd ')
Begin
Select @ maid = 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)
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

Example:
Table record:
Before execution:
CodeRuleID Head MiddleRule Length Tail RecordDay Record State
BookMark. RecordID B YYYYMMDD 6 20051124 2 1
Document. RecordID d yyyymmdd 6 20051025 1 1

Execution value:
Exec pGet_CodeRule 'bookmark. recordid ',''

Get:
Result: B20060125000001

Change table records:
CodeRuleID Head MiddleRule Length Tail RecordDay Record State
BookMark. RecordID B YYYYMMDD 6 20050125 1 1
Document. RecordID d yyyymmdd 6 20051025 1 1

I hope you will be inspired by the above tips :)