SQL Server primary key auto-generated _ table and stored procedure

Primary key table:

CREATE TABLE [dbo]. [KeyCode] (
[KeyName] [varchar] () not NULL,
[Keytablename] [varchar] (+) NULL,
[Keyfieldname] [varchar] (+) 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] (+) NULL,

(
[KeyName] Asc
) with (Pad_index = off, Statistics_norecompute = off, Ignore_dup_key = off, Allow_row_locks = on, Allow_page_locks = O N, FILLFACTOR = +) on [PRIMARY]
) on [PRIMARY]

Primary key Generation SQL:

Create Procedure [dbo]. [Sp_getkeycode] @KeyNameVarchar (), @KeyCodeVarchar Output, @IsReturnBit = 0asdeclare@zerostr as Varchar (30), @ Maxrunningcode as varchar (+), @KeyTableName as varchar (@KeyFieldName as varchar), @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, @tmpstr as varchar (+), @tempStr1 as varchar (40) , @sSQL as varchar, @CCode as Decimal (20,0), @RetStr as varchar, @CurrentKeyCode as varchar, @i as smallint, @Hea Dstr as Varchar (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 a 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 (suffi X, ') from keycode Where KeyName = @KeyName – to exclude alias from Keytablename (:D Elibase:delord-Delord) Set @table Namestartpos=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) Else beginset @tmpstr = Ca St ((@CurrentCode + 1) as Varchar) Set @CCode = @CurrentCode + 1Update keycodeSet Currentcode =cast (@CCode as Decimal (20,0)) where KeyName = @KeyNameset @TempStr1 = @HeadStr + SubString (@zerostr, 1, @Cod Elen-len (@tmpstr)) + @tmpstrif @CCode > @CodeMax---Find from beginningbeginif @IsReturn = 1 beginselect ' Error ' Keyna Me, ' All numbers is occupied! ' Keycodeset @KeyCode = ' returnendraiserror (' All numbers is occupied! ', 16,1) Select @KeyCode = ' return endend---Firstloo P <> ' 1 ' Set @retstr = @TmpStrend---codetype = ' 1 ' If @CodeType = ' 2 '---YY + xxxxxxbegin set @prefixStr = Substrin G (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 ((@CurrentCod E+1) as varchar (endif) @CodeType = ' 3 '--yy + MM + xxxxxxbeginset @PrefixStr = Cast (Month (GetDate ()) as varchar (2)) if L En (@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) 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 @prefi XSTR = 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) endif @CodeType = ' 5 '--Yyyy+mm+xxxxxbeginset @PrefixStr = Cast (Month (GetDate ()) as Varchar (2)) If Len (@PrefixStr) =1 Set @PrefixStr = ' 0 ' + @PrefixStrset @prefixStr = Cast (Year (getd Ate ()) as Varchar (4)) + @PrefIxstrif @KeyTableName <> @prefixStrbeginset @[email protected]-1Update keycode Set keytablename = @prefixStr , Currentcode = @CurrentCOdewhere KeyName = @KeyNameendset @retStr =cast ((@CurrentCode + 1) as Varchar) endIf @CodeType = ' 7 '--ddxxxx moorthy 19/10/00beginset @tmpStr = Cast (Day (GetDate ()) as Varchar (2)) Set @PrefixStr = @PRefixStr + replic Ate (' 0 ', 2-len (@TmpStr)) + @TmpStrif @KeyTableName <> @Prefixstrbeginset @CCode = @CodeMin -1update keycode Set Keyt Ablename = @prefixStr, Currentcode = @CodeMin -1where KeyName = @KeyNameendset @retStr = Cast ((@CurrentCode + 1) as Varchar (  ) Endif @CodeType = ' 8 '--xymxxxx----Running number reset Monthlybeginset @tmpStr = Cast (Right (year (GetDate ()), 1) As varchar (2)) if cast (month (GetDate ()) as Varchar (2)) = ' Ten ' Set @TmpStr = @TmpStr + ' A ' Else if cast (the month (Get   Date ()) as varchar (2)) = ' one ' 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, Cu Rrentcode = @CodeMin -1where KeyName = @KeyNameendset @retStr = cast ((@CCode + 1) as Varchar) Endif cast (@retstr as Deci Mal (20,0)) = @CodeMax--Modify currentcodeupdate keycode Set Currentcode = @CodeMin-1 WHERE KeyName = @KeyNameelseUpdat E keycode Set Currentcode =cast (@retstr as Decimal (20,0)) where KeyName = @KeyNameSet @retstr = @HeadStr + @PrefixStr + SubS Tring (@zeroStr, 1, @CodeLen-len (@retstr)) + @retstr + @SuffixSelect @KeyCode = @RetStrIf @IsReturn = 1 Beginselect @KeyName K Eyname, @KeyCode keycodeset @KeyCode = "Returnend

SQL Server primary key auto-generated _ table and stored procedure