SQL SERVER創建流水號的存儲過程

USE TEMP;
GO
/****** Object:  StoredProcedure [dbo].[SP_GetSeqNo]    Script Date: 2017/7/17 上午 09:28:32 ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

/*===================================================================  
Name:    GetSeqNo  
Description: 根據序號類型取得流水號  
Parameters:  
            @SEQ_TYPE 序號類型
            @SEQ_NO_CREATE 流水號創建格式

Return Values: 流水號  
Ref. Tables:  
            SeqNoStore  

Creation Date:  2016/12/15
Sample:   EXEC SP_GetSeqNo 'R' ,'1707000001'
================================================================== */
ALTER PROCEDURE dbo.SP_GetSeqNo
(
  @SEQ_TYPE VARCHAR(10),
  @SEQ_NO_CREATE INT = 1
)
AS
BEGIN
      --返回流水號
      DECLARE @RETURN_SEQ INT;
      --是否存在該類型
      DECLARE @IS_EXIST INT;

      SELECT
        @IS_EXIST = COUNT(SEQ_NO)
      FROM
        SeqNoStore
      WHERE
        SEQ_TYPE = @SEQ_TYPE;

      --是否存在該類型
      IF @IS_EXIST = 0
         BEGIN  
               SET @RETURN_SEQ = @SEQ_NO_CREATE;
                --新建流水號
               INSERT   INTO SeqNoStore
                        (
                          SEQ_NO,
                          SEQ_TYPE,
                          SEQ_CREATE_TIME
                        )
               VALUES
                        (
                          @SEQ_NO_CREATE,
                          @SEQ_TYPE,
                          GETDATE()
                        );
         END; 
      ELSE
         BEGIN 
               -- 定義變更記錄流水號
               DECLARE @SEQ_NO INT; 
               DECLARE @SEQ_UPDATE_TIME TIMESTAMP;

               SELECT
                @SEQ_NO = SEQ_NO,
                @SEQ_UPDATE_TIME = SEQ_UPDATE_TIME
               FROM
                SeqNoStore; 

               SET @RETURN_SEQ = @SEQ_NO + 1;

               --更新流水號,流水號+1
               UPDATE
                SeqNoStore
               SET
                SEQ_NO = @SEQ_NO + 1
               WHERE
                SEQ_UPDATE_TIME = @SEQ_UPDATE_TIME;

         END; 

    --如果返回值<1,再查詢
      IF ( @@ROWCOUNT < 1 )
         EXEC @SEQ_TYPE;

      ELSE
         SELECT
                @RETURN_SEQ AS SEQ;
END;

最後附上表結構:

CREATE TABLE [dbo].[SeqNoStore](
    [SEQ_NO] [INT] NOT NULL,
    [SEQ_TYPE] [VARCHAR](10) NOT NULL,
    [SEQ_CREATE_TIME] [DATE] NOT NULL,
    [SEQ_UPDATE_TIME] [TIMESTAMP] NOT NULL
)