SQL创建流水号

 

创建流水号表

CREATE TABLE SystemSerialNo
(
    SerialNoId INT PRIMARY KEY IDENTITY,
    TableName VARCHAR(60),
    Prefix VARCHAR(20),
    LatestDate VARCHAR(8),
    MaxSerialNo INT ,
    DataVersion timestamp
    
)

创建存储过程

-- Author:    <CallmeYhz,,Name>
-- Create date: <Create Date,,>
-- Description:  <Description,,>
-- [sp_GetSerialNumber] 'Test','CC',4,0
-- @ShowDate 0：编码模式 1：单号模式
-- =============================================
ALTER PROCEDURE [dbo].[sp_GetSerialNumber]  @tableName varchar(100),@Prefix VARCHAR(20),@PadLeft INT,@ShowDate BIT
AS
BEGIN
 SET NOCOUNT ON;
 BEGIN TRY
   BEGIN TRAN
     DECLARE @MaxValue INT,@dataVersion timestamp,@defaultDateTime VARCHAR(8)
     IF NOT EXISTS(SELECT * FROM SystemSerialNo  WHERE tableName=@tableName AND Prefix=@Prefix)
       BEGIN  
         INSERT INTO SystemSerialNo(TableName,Prefix,LatestDate,MaxSerialNo)
           VALUES (@tableName,@Prefix,CONVERT(VARCHAR(20),GETDATE(),112),1)
       END
     ELSE
       BEGIN
         SELECT @dataVersion=DataVersion,@defaultDateTime=LatestDate FROM SystemSerialNo WHERE tableName=@tableName AND Prefix=@Prefix
         --如果不显示日期，则为编码模式，最大编码加一，不受日期限制
         IF @ShowDate=0
            BEGIN
                UPDATE SystemSerialNo WITH(ROWLOCK) SET MaxSerialNo=MaxSerialNo+1
                WHERE tableName=@tableName  AND Prefix=@Prefix AND DataVersion=@dataVersion
            END
        ELSE
            BEGIN
             --如果在同一天，最大编码加一，否则更新日期并重置最大编码
             IF @defaultDateTime=CONVERT(VARCHAR(12),GETDATE(),112)
                BEGIN
                    UPDATE SystemSerialNo WITH(ROWLOCK) SET MaxSerialNo=MaxSerialNo+1
                    WHERE tableName=@tableName  AND Prefix=@Prefix AND DataVersion=@dataVersion
                END
            ELSE
                BEGIN 
                    UPDATE SystemSerialNo WITH(ROWLOCK) SET LatestDate=CONVERT(VARCHAR(12),GETDATE(),112),MaxSerialNo=1
                    WHERE tableName=@tableName  AND Prefix=@Prefix AND DataVersion=@dataVersion                
                END
            END 
       END  
     SELECT Prefix+(CASE @ShowDate WHEN 1 THEN RIGHT(LatestDate,6) ELSE '' END)+(RIGHT(replicate('0',@PadLeft)+CAST(MaxSerialNo AS VARCHAR(10)),@PadLeft))
         FROM SystemSerialNo WITH(XLOCK,PAGLOCK) WHERE tableName=@tableName  AND Prefix=@Prefix
    COMMIT TRAN
 END TRY
 BEGIN CATCH  
   ROLLBACK TRAN    
 END CATCH  
END

运行

 工作中有使用，在此备忘