SQL Server主键自动生成_表and存储过程

主键表:

1
CREATE TABLE [dbo].[KEYCODE](<br> [KeyName] [varchar](12) NOT NULL,<br> [KeyTableName] [varchar](40) NULL,<br> [KeyFieldName] [varchar](30) NULL,<br> [StrLen] [int] NULL,<br> [CodeLen] [int] NULL,<br> [CodeMin] [float] NULL,<br> [CodeMax] [float] NULL,<br> [CodeType] [varchar](1) NULL,<br> [CurrentCode] [float] NULL,<br> [FirstLoop] [varchar](1) NULL,<br> [Prefix] [varchar](6) NULL,<br> [Suffix] [varchar](6) NULL,<br> [Remarks] [varchar](500) NULL,<br> CONSTRAINT [PK_KEYCODE] PRIMARY KEY CLUSTERED <br>(<br> [KeyName] ASC<br>)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]<br>) ON [PRIMARY]

主键生成SQL:

Create Procedure [dbo].[SP_GetKeyCode]
    @KeyName    Varchar(20),
    @KeyCode    Varchar(30) Output,
    @IsReturn   Bit = 0
AS
Declare @zeroStr as Varchar(30),@MaxRunningCode as Varchar(30),
    @KeyTableName as Varchar(40),@KeyFieldName as Varchar(40),
    @CodeLen as Smallint,@CodeMin as Decimal(20,0),@CodeMax as Decimal(20,0),
    @CodeType as Varchar(1),@CurrentCode as Decimal(20,0),@FirstLoop as varchar(1),
    @Prefix as Varchar(6),@Suffix as Varchar(6),@preFixStr as Varchar(10),
    @tmpstr as Varchar(30),@tempStr1 as Varchar(40),@sSQL as Varchar(400),
    @CCode as Decimal(20,0), @RetStr as Varchar(20),@CurrentKeyCode as Varchar(30),
    @I as smallint, @HeadStr as Varchar(20)
DECLARE @TableNameStartPos  SmallInt
 
set @zeroStr = '000000000000000000000000000000'
Set @MaxRunningCode= '999999999999999999999999999999'
set @KeyName = Upper(@KeyName)
--set @HeadStr = upper(@HeadStr)
if not exists(Select * from KeyCode where KeyName = @KeyName)
Begin
    If @IsReturn = 1
    Begin
        Select @KeyName keyname, '' KeyCode
        Set @KeyCode = ''
        Return
    End
    RaisError('%s is an invalid KeyName!',16,1,@KeyName)
    Select @KeyCode = ''
    Return
end
 
Select  @KeyTableName = IsNull(KeyTableName,''),
    @KeyFieldName = IsNull(KeyFieldName,''),
    @CodeLen = IsNull(CodeLen,0),
    @CodeMin = Cast(IsNull(CodeMin,0) as Decimal(20,0)),
    @CodeMax = cast(IsNull(CodeMax,99) as Decimal(20,0)),
    @CodeType = IsNull(CodeType,'1'),
    @CurrentCode = Cast(IsNull(CurrentCode,0) as Decimal(20,0)),
    @FirstLoop = IsNull(FirstLoop,'0'),
    @Prefix = IsNull(Prefix,''),
    @Suffix = IsNull(Suffix,'')
From KeyCode Where KeyName = @KeyName
 
-- To exclude alias from KeyTableName (:DELIBASE:DELORD --> DELORD)
set @tableNameStartPos=Patindex('%BASE:%',@KeyTableName)
if @tableNameStartPos>0
    set @KeyTableName=subString(@KeyTableName,@TableNameStartPos+5,len(@KeyTableName)-@TableNameStartPos-4)
 
Set @HeadStr = @Prefix
 
Set @preFixStr=''
 
if @CodeType = '1'
Begin
    if @FirstLoop = '1'
        set @tmpstr = Cast((@CurrentCode + 1) as Varchar(20))
    else
    begin
        set @tmpstr = Cast((@CurrentCode+1) as Varchar(20))
        set @CCode = @CurrentCode + 1
        Update KeyCode set CurrentCode =Cast(@CCode as Decimal(20,0)) where keyname = @KeyName
        set @TempStr1= @HeadStr + SubString(@zerostr,1,@CodeLen - len(@tmpstr))+ @tmpstr
        if @CCode > @CodeMax ---Find from beginning
        begin
            If @IsReturn = 1
            Begin
                Select 'Error' KeyName, 'All numbers are occupied!' KeyCode
                Set @KeyCode = ''
                Return
            End
            raisError('All numbers are occupied!',16,1)
            Select @KeyCode = ''
            return
        end        
    end --- FirstLoop <>'1'
    set @retstr = @TmpStr
end  --- CodeType ='1'
if @CodeType = '2' --- YY + XXXXXX
Begin 
    set @prefixStr = SUbString(Cast(year(GetDate()) as Varchar(4)),3,2)
    if @KeyTableName <> @prefixStr
    begin
        set @CurrentCode = @COdeMin -1
        Update KeyCode set KeyTableName =  @prefixStr,CurrentCode = @CurrentCode
        where KeyName = @KeyName
    end
    set @retStr=Cast((@CurrentCode+1) as Varchar(10))
end
if @CodeType = '3' --YY + MM + XXXXXX
begin
    Set @PrefixStr = Cast(Month(GetDate()) as Varchar(2))
    if len(@PrefixStr)=1
        Set @PrefixStr = '0' + @PrefixStr
    set @prefixStr = SUbString(Cast(year(GetDate()) as Varchar(4)),3,2) + @PrefixStr
    if @KeyTableName <> @prefixStr
    begin
        set @CurrentCode=@CodeMin - 1
        Update KeyCode set KeyTableName =  @prefixStr,CurrentCode = @CurrentCOde
        where KeyName = @KeyName
    end
    set @retStr=Cast((@CurrentCode+1) as Varchar(10))
end
if @CodeType = '4'   -- YY+MM+DD+xxxxx
Begin
    Set @TmpStr = Cast(Month(GetDate()) as Varchar(2))
    if len(@tmpStr)=1
        Set @tmpStr = '0' + @tmpStr
    Set @tempStr1 = Cast(day(GetDate()) as Varchar(2))
    if len(@tempStr1)=1
        Set @tempStr1 = '0' + @tempStr1
    set @prefixStr = SUbString(Cast(year(GetDate()) as Varchar(4)),3,2) + @tmpStr+@tempStr1
    if @KeyTableName <> @prefixStr
    begin
        set @CurrentCode = @CodeMin -1
        Update KeyCode set KeyTableName =  @prefixStr,CurrentCode = @CurrentCode
        where KeyName = @KeyName
    end
    set @retStr=Cast((@CurrentCode+1) as Varchar(10))
end
if @CodeType = '5'  -- YYYY+MM+xxxxx
begin
    Set @PrefixStr = Cast(Month(GetDate()) as Varchar(2))
    if len(@PrefixStr)=1
        Set @PrefixStr = '0' + @PrefixStr
    set @prefixStr = Cast(year(GetDate()) as Varchar(4)) + @PrefixStr
    if @KeyTableName <> @prefixStr
    begin
        set @CurrentCode=@CodeMin - 1
        Update KeyCode set KeyTableName =  @prefixStr,CurrentCode = @CurrentCOde
        where KeyName = @KeyName
    end
    set @retStr=Cast((@CurrentCode+1) as Varchar(10))
end
 
If @CodeType = '7'   -- DDxxxx  Moorthy 19/10/00
Begin
    Set @tmpStr = Cast(Day(GetDate()) As Varchar(2))
    set @PrefixStr = @PRefixStr + replicate('0',2-len(@TmpStr)) + @TmpStr
    if @KeyTableName <> @Prefixstr
    begin
        set @CCode = @CodeMin - 1
        Update KeyCode set KeyTableName = @prefixStr,CurrentCode = @CodeMin - 1
        where KeyName = @KeyName
    end
    set @retStr = Cast((@CurrentCode+1) as Varchar(10))
End
 
if @CodeType = '8'    -- xYMxxxx ----  running number reset monthly
 
Begin
    Set @tmpStr = Cast(Right(Year(GetDate()),1) As Varchar(2))
    If Cast(Month(GetDate()) As Varchar(2)) = '10'
       Set @TmpStr = @TmpStr + 'A'
        Else If Cast(Month(GetDate()) As Varchar(2)) = '11'
       Set @TmpStr =  @TmpStr + 'B'
        Else If Cast(Month(GetDate()) As Varchar(2)) = '12'
       Set @TmpStr =  @TmpStr + 'C'
    Else
       Set @TmpStr =  @TmpStr + Cast(Month(GetDate()) As Varchar(2))
 
    set @PrefixStr = @PRefixStr + replicate('0',2-len(@TmpStr)) + @TmpStr
    if @KeyTableName <> @Prefixstr
    begin
        set @CCode = @CodeMin - 1
        Update KeyCode set KeyTableName = @prefixStr,CurrentCode = @CodeMin - 1
        where KeyName = @KeyName
    end
    set @retStr = Cast((@CCode+1) as Varchar(10))
End
 
 
 
if cast(@retstr as Decimal(20,0)) = @CodeMax  -- modify CurrentCode
    Update KeyCode set CurrentCode = @CodeMin -1 where KeyName = @KeyName
else
    Update KeyCode set CurrentCode =Cast(@retstr as Decimal(20,0)) where keyname = @KeyName
Set @retstr = @HeadStr+ @PrefixStr + SubString(@zeroStr,1,@CodeLen-len(@retstr))+ @retstr+ @Suffix
Select @KeyCode = @RetStr
 
If @IsReturn = 1
Begin
    Select @KeyName KeyName, @KeyCode KeyCode
    Set @KeyCode = ''
    Return
End

 

posted @   下雨天的马甲  阅读(743)  评论(0编辑  收藏  举报
编辑推荐:
· 深入理解 Mybatis 分库分表执行原理
· 如何打造一个高并发系统?
· .NET Core GC压缩(compact_phase)底层原理浅谈
· 现代计算机视觉入门之:什么是图片特征编码
· .NET 9 new features-C#13新的锁类型和语义
阅读排行:
· 《HelloGitHub》第 106 期
· Spring AI + Ollama 实现 deepseek-r1 的API服务和调用
· 数据库服务器 SQL Server 版本升级公告
· 深入理解Mybatis分库分表执行原理
· 使用 Dify + LLM 构建精确任务处理应用
点击右上角即可分享
微信分享提示