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 |
分类:
SQL
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 深入理解 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 构建精确任务处理应用