SQL 中 自定义流水码

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
--====================================================
--2024-01-31 流水码
--====================================================
ALTER PROC [dbo].[PROC_LED_UcodeSeqALL]
    @FType NVARCHAR(50) = '',
    @MAX INT = 0,
    @MIN INT = 0,
    @LEN INT = 0,
    @SeqNo VARCHAR(8) = '' OUTPUT
AS
BEGIN
    SET @SeqNo = '';
    --SELECT top 100 * FROM  UserCode ORDER BY CreateDate desc
    SELECT @SeqNo = UserCodeName
    FROM dbo.UserCode
    WHERE UserCodeDescription = @FType
          AND ParentUserCodeId = 'URC1000004DK';
        
    SET @SeqNo = ISNULL(@SeqNo, '');

    IF @SeqNo = ''
    BEGIN
        SET @SeqNo = RIGHT('0000000001', @LEN);

        INSERT INTO dbo.UserCode
        (
            UserCodeName,
            UserCodeDescription,
            ParentUserCodeId
        )
        VALUES
        (@SeqNo, @FType, 'URC1000004DK');

    END;
    ELSE
    BEGIN
        
        SET @SeqNo = RIGHT('0000000000' + CAST(CONVERT(INT, @SeqNo) + 1 AS VARCHAR(8)), @LEN);
        IF @MAX<2000
        BEGIN
            SET @SeqNo=2001
        END
        IF @MAX<=(SELECT  UserCodeName FROM dbo.UserCode WHERE ParentUserCodeId='URC1000004DK' AND  UserCodeDescription='GCSX_J+N' )
        BEGIN
            SET @SeqNo=2001
        END
        UPDATE dbo.UserCode
        SET UserCodeName = @SeqNo
        WHERE UserCodeDescription = @FType
              AND ParentUserCodeId = 'URC1000004DK';
    END;

END;



GO