序列表 批量的含义

USE DBname
GO
/****** Object:  StoredProcedure [dbo].[PROC_GETSEQUENCE_BATCH]    Script Date: 3/28/2019 3:55:58 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PROC_GETSEQUENCE_BATCH]
    @TABLENAME VARCHAR(20),
    @USERID VARCHAR(50),
    @BATCH_NUM INT,
    @SEQNUM INT OUTPUT
AS
DECLARE @STEP INT, @NEXTID INT
BEGIN
    UPDATE SSEQUENCE WITH (ROWLOCK)
    SET
        @NEXTID = SEQ_NUM = ISNULL(SEQ_NUM, SEQ_BOUNDARY_L) + SEQ_STEP * abs(ISNULL(@BATCH_NUM,0)),
        @STEP = SEQ_STEP,
        SEQ_UPDATE_DT = GETDATE(),
        SEQ_UPDATEUID = @USERID
    WHERE
        UPPER(SEQ_TABLE) = UPPER(@TABLENAME)
        AND SEQ_PARTITION_KEY = 128
    
    SELECT @SEQNUM = @NEXTID - @STEP * abs(ISNULL(@BATCH_NUM,0))
END

　　

在数据测试的时候，发现开发最容易犯的错是：

seq_num = 需要加的值+ seq_num  

在需要加的值这边用的是select count(*) from tablexxx, 会有null的场景出现，导致最终seq_num被更新为null+数字=null

转载于:https://www.cnblogs.com/qianjinyan/p/10616048.html