[SQL Server]序號(Sequence)產生器(可依不同需要開發)

需求:
1. 取得目前的CUR_SEQ
2. New CUR_SEQ= CUR_SEQ + 1 (超過6位數，會重置為1)
3. Update New CUR_STAN
4. 輸出 New CUR_STAN
限制: 確保每個Client取得的CUR_SEQ號碼牌為唯一...

需要如下方的結果:(A+B+SEQNO)
ID NO CUR_SEQ
----- ---- -------------
8 0 1
8 1 1
8 2 1
(3 個資料列受到影響)

想了一下，若要確保每個用戶端在同時間取同一組序號時，不得有重覆取得的問題，
但又不能影響其它組取得序號，因此覺得使用with(ROWLOCK)會是較好的決定:

With(ROWLOCK) : 若有table有規畫PK constraint或index時，則只會lock住該筆記錄，
不會影響其它組的資料存取。
ROWLOCK其實也並不是直接對該筆資料列作鎖定，而是針對其INDEX的KEY值作鎖定。
下方有online book說法
/*
Lock hints ROWLOCK, UPDLOCK, AND XLOCK that acquire row-level locks may place locks on index keys rather than the actual data rows. For example, if a table has a nonclustered index, and a SELECT statement using a lock hint is handled by a covering index, a lock is acquired on the index key in the covering index rather than on the data row in the base table.
*/

我的作法:
A．新增SEQUENCE Table: SEQNO

CREATE TABLE SEQNO(ID NVARCHAR(5) NOT NULL, NO NVARCHAR(2) NOT NULL, CUR_SEQ NUMERIC(6) NOT NULL, CONSTRAINT PK_SEQNO PRIMARY KEY (ID,NO));

B．新增SEQUENCE PROCEDURE:SP_SEQNO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_SEQNO]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_SEQNO]
GO

CREATE PROCEDURE SP_SEQNO (
@ID nvarchar(5),
@NO nvarchar(2),
@MAXSEQ NUMERIC(6) output
)
AS
DECLARE @CURSEQ NUMERIC(6);
SET @CURSEQ = 0;
BEGIN
/* 初始值*/
IF NOT EXISTS (SELECT * FROM SEQNO WHERE ID=@ID AND NO=@NO )
BEGIN
INSERT INTO SEQNO (ID,NO,CUR_SEQ) VALUES(@ID,@NO,0);

UPDATE SEQNO WITH(ROWLOCK)
SET @MAXSEQ=CUR_SEQ= CUR_SEQ+1
WHERE ID = @ID
AND NO= @NO;
END
ELSE
BEGIN
/*抓出目前的SEQNO放至@CURSEQ，最大值限制判斷 */
SELECT @CURSEQ = CUR_SEQ
FROM SEQNO WITH(ROWLOCK)
WHERE ID = @ID
AND NO= @NO

/* 若超過6位數需重置為1*/
IF (@CURSEQ =999999)
BEGIN

UPDATE SEQNO WITH(ROWLOCK)
SET @MAXSEQ=CUR_SEQ=0 WHERE ID =@ID AND NO =@NO;

END
ELSE
IF (@CURSEQ<999999)
BEGIN
UPDATE SEQNO WITH(ROWLOCK)
SET @MAXSEQ=CUR_SEQ=CUR_SEQ+1 WHERE ID =@ID AND NO =@NO;
END
END
END

參考資料: url= lock explain:http://technet.microsoft.com/en-us/library/ms187373.aspx
recieve varaible output valus from procedure: 如何接收 Store Procedure 的傳回值