[Nintex]月別の採番

お手軽にNintexでSQLを実行するのみです。採番を自作することで、色々な業務要件に対応する事が可能になります。

実行するプロシージャは下記の様な感じです。

CREATE PROCEDURE [dbo].[月別採番ストアド]
@KBNCODE INT
AS
DECLARE @SEQ [numeric]
DECLARE @MyKbn [numeric]
DECLARE @YM [varchar](6)
SET @MyKbn = @KBNCODE;
SET @YM = Convert(varchar(6),GETDATE(),112);
SET NOCOUNT ON
BEGIN TRAN TR1
SET @SEQ = (Select [SEQ] From 月別採番 WHERE [KBN] = @MyKbn AND [YYYMM] = @YM);
IF (@SEQ IS NULL)
BEGIN
INSERT INTO 月別採番 ([SEQ],[YYYMM],KBN) VALUES (1,@YM,@MyKbn)
END;
ELSE
BEGIN
UPDATE 月別採番 SET [SEQ] = SEQ + 1 WHERE [KBN] = @MyKbn AND [YYYMM] = @YM
END;
SELECT (SELECT [SEQ] FROM 月別採番 WHERE [KBN] = @MyKbn And [YYYMM] = @YM) AS SEQ
COMMIT TRAN TR1

データベースも事前に作成しておきましょう

CREATE TABLE [dbo].[月別採番](
    [KBN] [int] NOT NULL,
    [YYYMM] [varchar](6) NOT NULL,
    [SEQ] [numeric](18, 0) NULL,
CONSTRAINT [PK_月別採番] PRIMARY KEY CLUSTERED
(
    [KBN] ASC,
    [YYYMM] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]