SQL函數解決insert多筆自動流水號問題
在MSSQL的存儲過程中有時會有一次性插入多筆的情況，INSERT INTO......SELECT....
這時如果INSERT的TABLE中有自定義的業務流水號['BU'+日期+流水碼] ，則不得不用使用游標，然後調用自動單號存儲過程，因為SELECT中不得使用存儲過程作為一列的結果集返回，但函數是可以的
這裏的TABLE名稱必須明確，原因很簡單，MSSQL中對自定義函數有很多限制，如不能執行動態T-SQL, 不能對數據庫中表進行insert,update,delete操作[表變量可以]。
--SELECT DBO.FN_GETAUTOID('', 'ST3_PADD', 'PO', '2011/07/21', 4)
CREATE FUNCTION FN_GETAUTOID
(
@NO_COMP NVARCHAR(10), --公司
@TableName NVARCHAR(100), --表名
@PREFIX NVARCHAR(4), --前綴
@DT_TRN NVARCHAR(10), --日期
@ID_LEN TINYINT, --流水碼長度
@ROWNUM INT --當前行序號
)
RETURNS NVARCHAR(30)
AS
BEGIN
DECLARE @AUTOID NVARCHAR(30) --自動單號
DECLARE @PREFIX_ALL NVARCHAR(15) --前綴
IF @NO_COMP IS NULL
SET @NO_COMP=''
IF @PREFIX IS NULL
SET @PREFIX=''
IF @ID_LEN IS NULL
SET @ID_LEN=4
SET @DT_TRN=REPLACE(REPLACE(@DT_TRN, '/', ''), '-', '')
--前綴
SET @PREFIX_ALL=@PREFIX+@DT_TRN
--點數增加表
IF @TableName='ST3_PADD'
BEGIN
SELECT @AUTOID=@PREFIX_ALL+RIGHT('00000000'+CAST(ISNULL(MAX(CAST(RIGHT(NO_PO, @ID_LEN) AS INT)), 0)+@ROWNUM AS NVARCHAR), @ID_LEN)
FROM ST3_PADD
WHERE (NO_COMP=@NO_COMP OR @NO_COMP='')
AND NO_PO LIKE @PREFIX_ALL+'%'
END
/*其他表。。
ELSE IF @TableName=''
BEGIN
END*/
RETURN @AUTOID
END
CREATE FUNCTION FN_GETAUTOID
(
@NO_COMP NVARCHAR(10), --公司
@TableName NVARCHAR(100), --表名
@PREFIX NVARCHAR(4), --前綴
@DT_TRN NVARCHAR(10), --日期
@ID_LEN TINYINT, --流水碼長度
@ROWNUM INT --當前行序號
)
RETURNS NVARCHAR(30)
AS
BEGIN
DECLARE @AUTOID NVARCHAR(30) --自動單號
DECLARE @PREFIX_ALL NVARCHAR(15) --前綴
IF @NO_COMP IS NULL
SET @NO_COMP=''
IF @PREFIX IS NULL
SET @PREFIX=''
IF @ID_LEN IS NULL
SET @ID_LEN=4
SET @DT_TRN=REPLACE(REPLACE(@DT_TRN, '/', ''), '-', '')
--前綴
SET @PREFIX_ALL=@PREFIX+@DT_TRN
--點數增加表
IF @TableName='ST3_PADD'
BEGIN
SELECT @AUTOID=@PREFIX_ALL+RIGHT('00000000'+CAST(ISNULL(MAX(CAST(RIGHT(NO_PO, @ID_LEN) AS INT)), 0)+@ROWNUM AS NVARCHAR), @ID_LEN)
FROM ST3_PADD
WHERE (NO_COMP=@NO_COMP OR @NO_COMP='')
AND NO_PO LIKE @PREFIX_ALL+'%'
END
/*其他表。。
ELSE IF @TableName=''
BEGIN
END*/
RETURN @AUTOID
END
插入數據SQL同下面SQL語句類似：
INSERT INTO ST3_PADD(NO_COMP, NO_PO, NO_SRC, SEQ_SRC)
SELECT NO_COMP, dbo.FN_GETAUTOID(NO_COMP, 'ST3_PADD', 'PO', '2011/07/21', 4, ROW_NUMBER() OVER(ORDER BY NO_CLUB)), NO_CLUB, NO_SEQ
FROM ST3_CLUBMD
WHERE NO_COMP='0001'
AND NO_CLUB='FR201106290001'
SELECT * FROM ST3_PADD
SELECT NO_COMP, dbo.FN_GETAUTOID(NO_COMP, 'ST3_PADD', 'PO', '2011/07/21', 4, ROW_NUMBER() OVER(ORDER BY NO_CLUB)), NO_CLUB, NO_SEQ
FROM ST3_CLUBMD
WHERE NO_COMP='0001'
AND NO_CLUB='FR201106290001'
SELECT * FROM ST3_PADD
注：此例中表名及相關邏輯用了實際代碼，只作參照用
此例未考慮數據庫並發插入問題，此問題估計須用到鎖機制才能解決
