sql自动生成流水号

创建表

CREATE TABLE CreateSerialNo
(
    CreateSerialNoId INT PRIMARY KEY IDENTITY(1,1),
    TableName VARCHAR(60),
    FixedCharacter   VARCHAR(20),--FixedCharacter
    LatestDate VARCHAR(8),
    MaxSerialNo INT ,
    DataVersion timestamp
)

创建存储过程

-- =============================================
-- Author:    <HK0272>
-- Create date: <2023/03/13>
-- Description:  <自动生成流水号>
-- =============================================
CREATE PROCEDURE [dbo].[GetSerialNo_DoMethod] 
@SerialNo nvarchar(100)='' output,
@TableName varchar(100),--表名
@FixedCharacter VARCHAR(20),  --固定字符
@PadLeft INT,    --流水号位数
@ShowDate BIT, --1加年月日(A230313001)  0不加年月日(A001)
@RiseTime datetime --传入年月日
AS
BEGIN
 SET NOCOUNT ON;
 BEGIN TRY
     BEGIN TRAN
         --传入时间为空则自动获取当前时间
         IF ISNULL(@RiseTime,'')=''
         BEGIN
            SET @RiseTime=GETDATE()
         END 

         DECLARE @MaxValue INT,
         @DataVersion timestamp,
         @DefaultDateTime VARCHAR(8)

         IF NOT EXISTS(SELECT 1 FROM CreateSerialNo  WHERE TableName=@TableName AND FixedCharacter=@FixedCharacter)
         BEGIN
            INSERT INTO CreateSerialNo
                (
                    TableName,
                    FixedCharacter,
                    LatestDate,
                    MaxSerialNo
                )
            VALUES 
                (
                    @TableName,
                    @FixedCharacter,
                    CONVERT(VARCHAR(20),@RiseTime,112),
                    1
                )
         END
         ELSE
         BEGIN
            SELECT 
                @DataVersion=DataVersion,
                @DefaultDateTime=LatestDate 
            FROM CreateSerialNo 
            WHERE TableName=@TableName AND FixedCharacter=@FixedCharacter

            --如果不显示日期，不受日期限制,每次自增+1
            IF @ShowDate=0
            BEGIN
                UPDATE CreateSerialNo WITH(ROWLOCK) SET MaxSerialNo=MaxSerialNo+1
                WHERE TableName=@TableName  AND FixedCharacter=@FixedCharacter AND DataVersion=@DataVersion
            END
            ELSE
            BEGIN
                --如果在同一天，流水叫已，否则更新日期并重置最大流水号
                IF @DefaultDateTime=CONVERT(VARCHAR(12),@RiseTime,112)
                BEGIN
                    UPDATE CreateSerialNo WITH(ROWLOCK) SET MaxSerialNo=MaxSerialNo+1
                    WHERE TableName=@TableName  AND FixedCharacter=@FixedCharacter AND DataVersion=@DataVersion
                END
                ELSE
                BEGIN
                    UPDATE CreateSerialNo WITH(ROWLOCK) SET LatestDate=CONVERT(VARCHAR(12),@RiseTime,112),MaxSerialNo=1
                    WHERE TableName=@TableName  AND FixedCharacter=@FixedCharacter AND DataVersion=@DataVersion
                END
            END
        END
        SELECT 
            @SerialNo=FixedCharacter+(CASE @ShowDate WHEN 1 THEN RIGHT(LatestDate,6) ELSE '' END)+(RIGHT(replicate('0',@PadLeft)+CAST(MaxSerialNo AS VARCHAR(10)),@PadLeft))
        FROM CreateSerialNo WITH(XLOCK,PAGLOCK) 
        WHERE TableName=@TableName  AND FixedCharacter=@FixedCharacter
        
        
    COMMIT TRAN
 END TRY
 BEGIN CATCH
    ROLLBACK TRAN
 END CATCH
 return 0
END

调用

declare @SerialNo nvarchar(50)=''
declare @InspectDate datetime=getdate()
--获取编号
exec GetSerialNo_DoMethod 
    @SerialNo=@SerialNo output,
    @TableName='TestTableName',--表名
    @FixedCharacter='', --固定字符
    @PadLeft=3,   --流水号位数
    @ShowDate=1,   --是否包含时间 1：是（A230313001）0:否(A001)
    @RiseTime=@InspectDate--生成时间，为空默认抓当前时间
    
select @SerialNo