获取累加流水码

USE [数据DB名称]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Serialnumber]
@systemId int,
@companyId varchar(32),
@errorCode int output,
@errorMsg nvarchar(512) output
AS

DECLARE @MaxPlanID NVARCHAR(50)
DECLARE @NewPlanID NVARCHAR(50)
DECLARE @Prefix NVARCHAR(20)
DECLARE @Suffix INT
DECLARE @Result NVARCHAR(50)

BEGIN Try
--1.获取当天流水码
SELECT @MaxPlanID= Max(PlanID) FROM Hb_Plan_Order WHERE CAST(CreateDate AS DATE) = CAST(GETDATE() AS DATE);
IF @MaxPlanID IS NULL
BEGIN
--2.没有获取到就插入新的流水码
Select @Result= CONVERT(varchar(100), GETDATE(), 112)+'.001'
SELECT @errorCode=0,@errorMsg='ok'
SELECT @Result as 'PlanID';
END
ELSE
BEGIN
--3.获取到了就累加流水码
-- 提取前缀部分
SET @Prefix = LEFT(@MaxPlanID, CHARINDEX('.', @MaxPlanID) - 1)

-- 提取后缀部分并转换为整数
SET @Suffix = CAST(SUBSTRING(@MaxPlanID, CHARINDEX('.', @MaxPlanID) + 1, LEN(@MaxPlanID)) AS INT)

-- 累加流水码
SET @Suffix = @Suffix + 1

-- 重新组合前缀和后缀生成新的流水码
SET @NewPlanID = @Prefix + '.' + RIGHT('00' + CAST(@Suffix AS NVARCHAR(3)), 3)

-- 设置结果变量
SET @Result = @NewPlanID
SELECT @errorCode=0,@errorMsg='ok'
SELECT @Result as 'PlanID';
END

END Try
BEGIN CATCH
-- 异常处理
SELECT @errorCode = ERROR_NUMBER(),@errorMsg = ERROR_MESSAGE();
END CATCH;