SQL Server主鍵自動產生_表and預存程序

標籤：

主鍵表：

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

主鍵產生SQL：

Create Procedure [dbo].[SP_GetKeyCode]@KeyNameVarchar(20),@KeyCodeVarchar(30) Output,@IsReturnBit = 0ASDeclare@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 @TableNameStartPosSmallIntset @zeroStr = ‘000000000000000000000000000000‘Set @MaxRunningCode= ‘999999999999999999999999999999‘set @KeyName = Upper(@KeyName)--set @HeadStr = upper(@HeadStr)if not exists(Select * from KeyCode where KeyName = @KeyName) BeginIf @IsReturn = 1 BeginSelect @KeyName keyname, ‘‘ KeyCodeSet @KeyCode = ‘‘ReturnEndRaisError(‘%s is an invalid KeyName!‘,16,1,@KeyName)Select @KeyCode = ‘‘ReturnendSelect@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>0set @KeyTableName=subString(@KeyTableName,@TableNameStartPos+5,len(@KeyTableName)[email protected])Set @HeadStr = @PrefixSet @preFixStr=‘‘if @CodeType = ‘1‘Beginif @FirstLoop = ‘1‘ set @tmpstr = Cast((@CurrentCode + 1) as Varchar(20))else beginset @tmpstr = Cast((@CurrentCode+1) as Varchar(20))set @CCode = @CurrentCode + 1Update KeyCode set CurrentCode =Cast(@CCode as Decimal(20,0)) where keyname = @KeyNameset @TempStr1= @HeadStr + SubString(@zerostr,1,@CodeLen - len(@tmpstr))+ @tmpstrif @CCode > @CodeMax ---Find from beginningbeginIf @IsReturn = 1 BeginSelect ‘Error‘ KeyName, ‘All numbers are occupied!‘ KeyCodeSet @KeyCode = ‘‘ReturnEndraisError(‘All numbers are occupied!‘,16,1)Select @KeyCode = ‘‘return endend --- FirstLoop <>‘1‘set @retstr = @TmpStrend  --- CodeType =‘1‘if @CodeType = ‘2‘ --- YY + XXXXXXBegin  set @prefixStr = SUbString(Cast(year(GetDate()) as Varchar(4)),3,2)if @KeyTableName <> @prefixStr beginset @CurrentCode = @COdeMin -1Update KeyCode set KeyTableName =  @prefixStr,CurrentCode = @CurrentCodewhere KeyName = @KeyNameendset @retStr=Cast((@CurrentCode+1) as Varchar(10))endif @CodeType = ‘3‘ --YY + MM + XXXXXXbeginSet @PrefixStr = Cast(Month(GetDate()) as Varchar(2))if len(@PrefixStr)=1 Set @PrefixStr = ‘0‘ + @PrefixStrset @prefixStr = SUbString(Cast(year(GetDate()) as Varchar(4)),3,2) + @PrefixStrif @KeyTableName <> @prefixStrbeginset @[email protected] - 1Update KeyCode set KeyTableName =  @prefixStr,CurrentCode = @CurrentCOdewhere KeyName = @KeyNameendset @retStr=Cast((@CurrentCode+1) as Varchar(10))endif @CodeType = ‘4‘   --YY+MM+DD+xxxxxBeginSet @TmpStr = Cast(Month(GetDate()) as Varchar(2))if len(@tmpStr)=1 Set @tmpStr = ‘0‘ + @tmpStrSet @tempStr1 = Cast(day(GetDate()) as Varchar(2))if len(@tempStr1)=1 Set @tempStr1 = ‘0‘ + @tempStr1set @prefixStr = SUbString(Cast(year(GetDate()) as Varchar(4)),3,2) + @[email protected]if @KeyTableName <> @prefixStr beginset @CurrentCode = @CodeMin -1 Update KeyCode set KeyTableName =  @prefixStr,CurrentCode = @CurrentCodewhere KeyName = @KeyNameendset @retStr=Cast((@CurrentCode+1) as Varchar(10))endif @CodeType = ‘5‘  -- YYYY+MM+xxxxxbeginSet @PrefixStr = Cast(Month(GetDate()) as Varchar(2))if len(@PrefixStr)=1 Set @PrefixStr = ‘0‘ + @PrefixStrset @prefixStr = Cast(year(GetDate()) as Varchar(4)) + @PrefixStrif @KeyTableName <> @prefixStrbeginset @[email protected] - 1Update KeyCode set KeyTableName =  @prefixStr,CurrentCode = @CurrentCOdewhere KeyName = @KeyNameendset @retStr=Cast((@CurrentCode+1) as Varchar(10))endIf @CodeType = ‘7‘   -- DDxxxx  Moorthy 19/10/00BeginSet @tmpStr = Cast(Day(GetDate()) As Varchar(2))set @PrefixStr = @PRefixStr + replicate(‘0‘,2-len(@TmpStr)) + @TmpStrif @KeyTableName <> @Prefixstrbeginset @CCode = @CodeMin - 1Update KeyCode set KeyTableName = @prefixStr,CurrentCode = @CodeMin - 1where KeyName = @KeyNameendset @retStr = Cast((@CurrentCode+1) as Varchar(10))Endif @CodeType = ‘8‘    -- xYMxxxx ----  running number reset monthlyBeginSet @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)) + @TmpStrif @KeyTableName <> @Prefixstrbeginset @CCode = @CodeMin - 1Update KeyCode set KeyTableName = @prefixStr,CurrentCode = @CodeMin - 1where KeyName = @KeyNameendset @retStr = Cast((@CCode+1) as Varchar(10))Endif cast(@retstr as Decimal(20,0)) = @CodeMax  -- modify CurrentCodeUpdate KeyCode set CurrentCode = @CodeMin -1 where KeyName = @KeyNameelseUpdate KeyCode set CurrentCode =Cast(@retstr as Decimal(20,0)) where keyname = @KeyNameSet @retstr = @HeadStr+@PrefixStr + SubString(@zeroStr,1,@CodeLen-len(@retstr))+ @retstr+ @SuffixSelect @KeyCode = @RetStrIf @IsReturn = 1 BeginSelect @KeyName KeyName, @KeyCode KeyCodeSet @KeyCode = ‘‘ReturnEnd

 

SQL Server主鍵自動產生_表and預存程序