簡單的取流水單號Stored Procedure

2009-12-09

14146
0
SQL
2013-10-01

簡單的取流水號Stored Procedure

前言

我們常常會在某Table中放入Internal的流水號，有時只是要不重覆的流水號而已，那這個SP就可以達到我們的要求哦!

實作

建立流水號檔(SYSTEM_SEQ_NO)
CREATE TABLE dbo.[SYSTEM_SEQ_NO] (
    [TYPE] [varchar] (12) NULL ,
    [PREFIX] [varchar] (20) NULL ,
    [CURRENT_NO] [int] NULL 
)

建立取流水號的SP(USP_GET_SYS_SEQ_NO)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[USP_GET_SYS_SEQ_NO]') 
    and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[USP_GET_SYS_SEQ_NO]
GO

CREATE PROCEDURE dbo.USP_GET_SYS_SEQ_NO
/****************************************************************************************
'程式代號：dbo.USP_GET_SYS_SEQ_NO 
'程式名稱：取得流水單號
'目　　的：
'參數說明：
(
@TYPE varchar(12)='', --類別
@PREFIX varchar(20)='', -- 前置詞
@NO_LENGTH int = 5 , --流水號長度
@COUNT int=1           --取得筆數
)    
'範　例　：
    EXEC USP_GET_SYS_SEQ_NO '財務', 'PREFIX', 5, 1
    EXEC USP_GET_SYS_SEQ_NO '財務', 'PREFIX', 5, 3
****************************************************************************************/
(
@TYPE varchar(12)='',   --申請類別
@PREFIX varchar(20)='', -- 前置詞
@NO_LENGTH int=5, --流水號長度
@COUNT int=1  --取得筆數
)
AS BEGIN
  DECLARE @TO_NO INT

  --檢查流水號是否存在
  IF NOT EXISTS
    (SELECT * FROM SYSTEM_SEQ_NO(NOLOCK) WHERE TYPE=@TYPE AND PREFIX=@PREFIX) 
  BEGIN
    INSERT INTO SYSTEM_SEQ_NO VALUES(@TYPE, @PREFIX, 0)
  END

  BEGIN TRAN
      --取出流水號並更新資料
      UPDATE SYSTEM_SEQ_NO
         SET CURRENT_NO = CURRENT_NO+@COUNT
           , @TO_NO = CURRENT_NO+@COUNT
       WHERE TYPE=@TYPE
         AND PREFIX=@PREFIX
  COMMIT TRAN

  SELECT FROM_NO=@PREFIX + RIGHT('0000000000' + CONVERT(VARCHAR, @TO_NO-@COUNT+1), @NO_LENGTH)
       , TO_NO=@PREFIX + RIGHT('0000000000' + CONVERT(VARCHAR, @TO_NO), @NO_LENGTH)
END

之後就可以呼叫SP取得流水號了!

在其他的SP中使用
--建立流水號變數
DECLARE @NEW_SEQ_NO VARCHAR(20)
--建立暫存檔來取得流水號
CREATE TABLE #TMP_SEQ_NO (FROM_NO varchar(100),TO_NO VARCHAR(100))
INSERT INTO #TMP_SEQ_NO(FROM_NO,TO_NO)
    EXEC USP_GET_SYS_SEQ_NO '財務', 'PREFIX', 5, 3
--從暫存檔中取得流水號
SELECT @NEW_SEQ_NO = FROM_NO
    FROM #TMP_SEQ_NO
SELECT @NEW_SEQ_NO