Sqlserver存储过程生成流水号，格式：年份+月份+日+序号，eg:20240228001

第一步：建表

CREATE TABLE [dbo].[udt_Setting_NextNumber](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[SiteCode] [nvarchar](50) NULL,
	[Building] [nvarchar](50) NULL,
	[Code] [nvarchar](50) NULL,
	[Name] [nvarchar](200) NULL,
	[N_Format] [nvarchar](2000) NULL,
	[N_Length] [int] NULL,
	[N_Max_Value] [int] NULL,
	[N_MaxAlarm] [int] NULL,
	[N_Min_Value] [int] NULL,
	[N_Current_Value] [int] NULL,
	[N_Increase] [int] NULL,
	[N_IsReset] [varchar](20) NULL,
	[N_Reset_Unit] [nvarchar](50) NULL,
	[N_LastRecordTime] [datetime] NULL,
	[CreateBy] [nvarchar](50) NULL,
	[CreationTime] [datetime] NULL,
	[UpdateBy] [nvarchar](50) NULL,
	[UpdateTime] [datetime] NULL,
	[StatusID] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[udt_Setting_NextNumber] ADD  CONSTRAINT [DF_udt_Setting_NextNumber_StatusID]  DEFAULT ((1)) FOR [StatusID]

第二步：插入数据，序号生成规则

![外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传](https://img-home.csdnimg.cn/images/20230724024159.png?origin_url=https%3A%2F%2Fimg-%E7%82%B9%E5%87%BBblog.csdnimg.cn%2Fdirect%2Fdbdc87c5c2c34e72b72cb462549c65fc.png&pos_id=img-pLhnArwm7c5c2c34e72b72cb462549c65fc.png
第三步：创建存储过程

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_FGWH_Setting_NextNumber] 
     @SiteCode NVARCHAR(50),
	 @Building NVARCHAR(50),
	 @Code NVARCHAR(50),
	 @NextNumber VARCHAR(100) = '' OUTPUT
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @ID INT
		,@N_Format NVARCHAR(2000)
		,@N_Length INT
		,@N_Max_Value INT
		,@N_Min_Value INT
		,@N_Current_Value INT
		,@N_Increase INT
		,@N_IsReset VARCHAR(20)
		,@N_Reset_Unit NVARCHAR(50)
		,@N_LastRecordTime DATETIME
	DECLARE @Next_Value INT
		,@Now DATETIME = getdate()
	DECLARE @IsReset BIT = 0
	DECLARE @YYYY VARCHAR(4) -->[YYYY]  2014
	DECLARE @YY_Num VARCHAR(2) -->[YY] 14
	DECLARE @MMM VARCHAR(3) -->[MMM] JAN
	DECLARE @MM_Num VARCHAR(2) -->[MM] 01
	DECLARE @WW_Num VARCHAR(2) -->[WW] 01
	DECLARE @DD_Num VARCHAR(2) -->[DD] 01
	DECLARE @THH_Num VARCHAR(2) -->[THH] 01
	DECLARE @TMM_Num VARCHAR(2) -->[TMM] 01
	DECLARE @TSS_Num VARCHAR(2) -->[TSS] 01
	DECLARE @TMS_Num VARCHAR(2) -->[TMS] 01

	SELECT @ID = ID
		,@N_Format = N_Format
		,@N_Length = N_Length
		,@N_Max_Value = N_Max_Value
		,@N_Min_Value = N_Min_Value
		,@N_Current_Value = N_Current_Value
		,@N_Increase = N_Increase
		,@N_IsReset = N_IsReset
		,@N_Reset_Unit = N_Reset_Unit
		,@N_LastRecordTime = N_LastRecordTime
	 FROM dbo.udt_Setting_NextNumber
	WHERE SiteCode = @SiteCode and Building = @Building and Code = @Code

	--if @ID is null return
	SELECT @YYYY = cast(DATEPART(Year, @Now) AS VARCHAR(4))
		,@YY_Num = RIGHT(cast(DATEPART(Year, @Now) AS VARCHAR(4)), 2)
		,@MMM = LEFT(UPPER(DATENAME(month, @Now)), 3)
		,@MM_Num = RIGHT('00' + cast(DATEPART(Month, @Now) AS VARCHAR), 2)
		,@WW_Num = RIGHT('00' + cast(DATEPART(week, @Now) AS VARCHAR), 2)
		,@DD_Num = RIGHT('00' + cast(DATEPART(DAY, @Now) AS VARCHAR), 2)
		,@THH_Num = RIGHT('00' + cast(DATEPART(HOUR, @Now) AS VARCHAR), 2)
		,@TMM_Num = RIGHT('00' + cast(DATEPART(MINUTE, @Now) AS VARCHAR), 2)
		,@TSS_Num = RIGHT('00' + cast(DATEPART(SECOND, @Now) AS VARCHAR), 2)
		,@TMS_Num = RIGHT('00' + cast(DATEPART(MS, @Now) AS VARCHAR), 2)

	IF @N_IsReset = 'Y'
	BEGIN
		SELECT @IsReset = CASE 
				WHEN @N_Reset_Unit = 'DAY'
					AND cast(@N_LastRecordTime AS DATE) != cast(@Now AS DATE)
					AND CHARINDEX('[DD]', @N_Format, 0) > 0
					THEN 1
				WHEN @N_Reset_Unit = 'WEEK'
					AND (
						(DATEPART(YEAR, @N_LastRecordTime) != DATEPART(YEAR, @Now))
						OR (DATEPART(WEEK, @N_LastRecordTime) != DATEPART(WEEK, @Now))
						)
					AND CHARINDEX('[WW]', @N_Format, 0) > 0
					THEN 1
				WHEN @N_Reset_Unit = 'MONTH'
					AND convert(VARCHAR(7), @N_LastRecordTime, 120) != convert(VARCHAR(7), @Now, 120)
					AND (
						CHARINDEX('[MM]', @N_Format, 0) > 0
						OR CHARINDEX('[MMM]', @N_Format, 0) > 0
						)
					THEN 1
				WHEN @N_Reset_Unit = 'YEAR'
					AND DATEPART(YEAR, @N_LastRecordTime) != DATEPART(YEAR, @Now)
					AND (
						CHARINDEX('[YY]', @N_Format, 0) > 0
						OR CHARINDEX('[YYYY]', @N_Format, 0) > 0
						)
					THEN 1
				ELSE 0
				END
	END

	PRINT @IsReset

	IF @IsReset = 1
		SET @Next_Value = @N_Min_Value
	ELSE
		SET @Next_Value = @N_Current_Value + @N_Increase

	IF @Next_Value > @N_Max_Value
		RETURN

	UPDATE udt_Setting_NextNumber
	SET N_LastRecordTime = @Now
		,N_Current_Value = @Next_Value
	WHERE ID = @ID

	SET @NextNumber = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@N_Format, '[YYYY]', @YYYY), '[YY]', @YY_Num), '[MMM]', @MMM), '[MM]', @MM_Num), '[WW]', @WW_Num), '[DD]', @DD_Num), '[THH]', @THH_Num), '[TMM]', @TMM_Num), '[TSS]', @TSS_Num), 'TMS', @TMS_Num), '[SITECODE]', @SiteCode) + RIGHT('00000000000000000' + cast(@Next_Value AS VARCHAR), @N_Length)

	 

END

最后一步：调用存储过程，产生流水号

   declare @ShipNumber varchar(50) exec [dbo].[usp_Setting_NextNumber] 'DMN','B11','ShipNumber',@ShipNumber OUTPUT 
   select ShipNumber=@ShipNumber
![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/5c1a120dabe54ab3a82895a62a5cee09.png)