SQL Server gets the stored procedure for serial number

Create a serial number table

CREATE TABLESystemserialno (serialnoidINT PRIMARY KEY IDENTITY, TableNameVARCHAR( -), PrefixVARCHAR( -), LatestdateVARCHAR(8), MaxserialnoINT, Dataversiontimestamp
    
)

Create a stored procedure

--Author: <CallmeYhz,,Name>--Create Date: <create date,,>--Description: <Description,,>--[Sp_getserialnumber] ' Test ', ' CC ', 4,0--@ShowDate 0: Encoding mode 1: Number mode--=============================================AlterPROCEDURE[Dbo].[Sp_getserialnumber]@tableNamevarchar100),@PrefixVARCHAR (20),@PadLeftInt@ShowDateBITAsBEGINSET NOCOUNTOn;BEGINTRYBEGINTRANDECLARE@MaxValueInt@dataVersionTimestamp@defaultDateTimeVARCHAR (8)IFNotEXISTS (SELECT*From SystemserialnoWHERE TableName=@tableNameand Prefix=@Prefix)BEGININSERTIntoSystemserialno (Tablename,prefix,latestdate,maxserialno)VALUES (@tableName,@Prefix,CONVERT (VARCHAR (20),GETDATE (),112),1)ENDELSEBEGINSELECT@dataVersion=dataversion,@defaultDateTime=latestdateFrom SystemserialnoWHERE TableName=@tableNameand Prefix=@Prefix--If the date is not displayed, the encoding mode, the maximum encoding plus one, is not limited by the dateIF@ShowDate=0BEGINUPDATE SystemserialnoWith (Rowlock)SET Maxserialno=maxserialno+1WHERE TableName=@tableNameand Prefix=@Prefixand dataversion=@dataVersionENDELSEBEGIN--If on the same day, the maximum encoding plus one, otherwise update the date and reset the maximum encodingIF@defaultDateTime=CONVERT (VARCHAR (12),GETDATE (),112)BEGINUPDATE SystemserialnoWith (Rowlock)SET Maxserialno=maxserialno+1WHERE TableName=@tableNameand Prefix=@Prefixand dataversion=@dataVersionENDELSEBEGINUPDATE SystemserialnoWith (Rowlock)SET latestdate=CONVERT (VARCHAR (12),GETDATE (),Maxserialno),=1WHERE TableName=@tableNameand Prefix=@Prefixand dataversion=@dataVersionENDENDENDSELECT Prefix+(Case@ShowDateWhen1ThenRight (Latestdate,6)ELSE‘‘END)+(Right (Replicate‘0‘,@PadLeft)+CAST (MaxserialnoAsVARCHAR ( @PadLeft from systemserialno with (xlock,paglock) where tablename= @tableName and prefix= @Prefix Span style= "color: #0000ff;" >commit tran end TRY Span style= "color: #0000ff;" >begin CATCH rollback  TRAN end CATCH end  

Run

Work in use, this memo

SQL Server gets the stored procedure for serial number