システム開発備忘録 SQL SERVER SEQUENCEの真似をする

番号をインクリメントする仕組み（テーブル＋ストアド）の作成
======================================================================
ORACLEのSEQUENCEと同じことをSQLサーバーで実現する
・インクリメント処理を毎回記述するのは面倒なので、パラメータで指定したシーケンスから
番号を取得するストアドを作成する。
・複数テーブルで共有の番号を発行したり、番号取得からインサートまでに時差がある場合に
シーケンステーブルを作成しておくのが有効。
======================================================================
ORACLEの場合は次のようにSEQUENCEとFUNCTIONが利用可能

-- =============================================
-- SEQUENCE作成
-- =============================================

/*出荷伝票番号*/
CREATE SEQUENCE SEQ_IN_DNP_NO
INCREMENT BY 1
START WITH 1
MAXVALUE 499999
/

/*入荷伝票番号*/
CREATE SEQUENCE SEQ_OUT_DNP_NO
INCREMENT BY 1
START WITH 500000
MAXVALUE 999999
/

-- =============================================
-- FUNCTION作成
-- =============================================

/*シーケンスからの番号取得*/
CREATE OR REPLACE FUNCTION F_GET_SEQUENCE
(SEQ_TYPE IN NUMBER)
RETURN
  NUMBER -- 戻り値のデータ型
IS
  SEQ_NUM NUMBER;
BEGIN
  /*SEQ_TYPEより各SEQUENSEから数値を取得 */
  IF SEQ_TYPE = 1 THEN
    SELECT SEQ_IN_DNP_NO.NEXTVAL INTO SEQ_NUM FROM DUAL;

  ELSIF SEQ_TYPE = 2 THEN
    SELECT SEQ_OUT_DNP_NO.NEXTVAL INTO SEQ_NUM FROM DUAL;

  END IF;

  RETURN SEQ_NUM;
END;

-- =============================================
-- SQLで伝票番号を取得する
-- =============================================

/*FUNCTION呼び出し*/
select F_GET_SEQUENCE(1) from DUAL

-- =============================================
-- テーブル作成
-- =============================================

--入荷伝票番号テーブル
CREATE TABLE [SEQ_IN_DEN_NO](
  [SEQ_VALUE] [int] NOT NULL
) ON [PRIMARY]

--出荷伝票番号テーブル
CREATE TABLE [SEQ_OUT_DEN_NO](
  [SEQ_VALUE] [int] NOT NULL
) ON [PRIMARY]

-- =============================================
-- ストアド作成
-- =============================================
CREATE PROCEDURE [SP_GET_SEQUENCE] (@mode int)
AS
BEGIN
  declare @ret int
  declare @maxV int --最大値
  declare @minV int --最小値
  declare @inc int --インクリメント

  if @mode=1
   --入荷伝票番号の取得（~499999）
   begin
     set @maxV = 499999
     set @minV = 000001
     set @inc = 1

     begin transaction

     --番号取得
     SET LOCK_TIMEOUT 1000
     SELECT @ret=SEQ_VALUE from [SEQ_IN_DEN_NO] WITH(TABLOCKX)

     if @ret > @maxV
       set @ret = -1
     else if @ret < @minV
       set @ret = -1
     else
       begin

       update [SEQ_IN_DEN_NO]
       set SEQ_VALUE=@ret + @inc

       end

     commit
   end

  else if @mode=2
   --出荷伝票番号の取得（~99999）
   begin
     set @maxV = 500000
     set @minV = 999999
     set @inc = 1

     begin transaction

     --番号取得
     SET LOCK_TIMEOUT 1000
     SELECT @ret=SEQ_VALUE from [SEQ_OUT_DEN_NO] WITH(TABLOCKX)

     if @ret > @maxV
       set @ret = -1
     else if @ret < @minV
       set @ret = -1
     else
       begin

       update [SEQ_OUT_DEN_NO]
       set SEQ_VALUE=@ret + @inc

       end

     commit
   end

  else
   set @ret = -1

  return @ret

END

VB2005でのストアド起動と戻り値を取得する関数

'''''''''''''''''''''''''''''''''''
'シーケンスから値を取得する
'引数：objConコネクションオブジェクト
'引数：lngSeqTypeシーケンスのタイプ（ストアドの@modeに渡す値）
'引数（戻り値）：strSeqNoシーケンス番号
'戻り値：エラーのときメッセージ
'''''''''''''''''''''''''''''''''''
Public Function GetDenNo_SqlServer(ByRef objCon As SqlConnection, ByVal lngSeqType As Long, ByRef strSeqNo As String) As String
    Dim strMsg As String = ""

    Dim objSC As New System.Data.SqlClient.SqlCommand

    Try

        'コマンドの設定（接続とストアドの指定）
        objSC.Connection = objCon
        objSC.CommandType = CommandType.StoredProcedure
        objSC.CommandText = "[dbo].[SP_GET_SEQUENCE]"
        'パラメータ（呼び出しモード）
        objSC.Parameters.Add(New SqlParameter("@mode", SqlDbType.Int, 4))
        objSC.Parameters("@mode").Value = lngSeqType
        '戻り値（シーケンス番号）用のパラメータ
        objSC.Parameters.Add(New SqlParameter("RetValue", SqlDbType.Int, 4))
        objSC.Parameters("RetValue").Direction = ParameterDirection.ReturnValue
        'SQL実行
        objSC.ExecuteNonQuery()

        strSeqNo = objSC.Parameters("RetValue").Value

    Catch ex As Exception
        If strMsg = "" Then strMsg = ex.Message
    Finally
    End Try

    Return strMsg

End Function

→オートナンバー列をSQLサーバーで作成する