使用儲存程序實現自訂遞增 ID（替代 Identity 欄位）

情境說明

若您需要自訂遞增邏輯（例如起始值 100、遞增 1），且不想使用資料表的 IDENTITY 欄位，可透過儲存程序配合輔助資料表實現線程安全的序號產生器。

資料表結構

CREATE TABLE common_info (
    name VARCHAR(10) PRIMARY KEY,
    value INT NOT NULL
);

INSERT INTO common_info VALUES ('msg_count', 100); -- 起始值

儲存程序實作

CREATE PROCEDURE p_ins_alm_msg
    @msg_count INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    -- 取得目前序號
    SELECT @msg_count = value 
    FROM common_info 
    WHERE name = 'msg_count';

    -- 原子性更新：僅當值未被其他交易修改時才成功
    UPDATE common_info 
    SET value = @msg_count + 1 
    WHERE name = 'msg_count' AND value = @msg_count;

    -- 若影響列數為 0，表示並發衝突，重試或報錯
    IF @@ROWCOUNT = 0
        RETURN -1000;

    -- 將新序號寫入目標資料表（示範）
    INSERT INTO table1 (field0) VALUES (@msg_count);

    RETURN 0;
END

關鍵點解析

• 樂觀並發控制： WHERE value = @msg_count 確保更新基於讀取時的值，若已被其他交易修改，本次更新失敗（@@ROWCOUNT=0），呼叫端需重試。
• 輸出參數： 透過 OUTPUT 參數將產生的序號回傳給呼叫端。
• 擴充性： 同一張 common_info 可支援多種序號（如 order_id, invoice_no），只需新增不同 name 列。

呼叫範例

DECLARE @new_id INT;
EXEC p_ins_alm_msg @msg_count = @new_id OUTPUT;
PRINT '新序號: ' + CAST(@new_id AS VARCHAR);

注意事項

高併發場景下，頻繁重試可能影響效能。若序號需求極大，建議改用 SEQUENCE 物件 或 IDENTITY 欄位，或採用號段預分配策略。