Use User-Defined Functon for default constraint使用Function產生自訂的自動編號欄位

開發人員需要每日Import大量資料,想改用SSIS產生package排程執行,但有一個流程目前是用EXCEL完成的,第一個欄位要自訂格式,年月加上流水號(例如200805000001),希望我在SSIS找到解決方案,當我聽到時第一個想法是透過T-SQL User-Defined Function與default contraint來完成這個需求,果然在google上找到類似的寫法,以下是我改寫後的demo code

--STEP1.Create test database
Use master
GO
CREATE DATABASE TESTDB
GO


--STEP2.Create T-SQL User-Defined Function
-- 在Code裡面已經指定要從這個資料表dbo.autoIDTable取得最大的流水號

USE TESTDB
GO
CREATE FUNCTION GetNewAutoID
(
-- Add the parameters for the function here
--@p1 char(4)
)
RETURNS char(12)
AS
BEGIN
-- Declare the return variable here
DECLARE @ResultVar Char(12)
Declare @MaxValue int
Set @MaxValue=0
Select @MaxValue=Cast(Right(autoid,6) as int) from dbo.autoIDTable
Set @MaxValue=@MaxValue +1
Set @ResultVar=LEFT(cast(convert(varchar , GETDATE(), 112) as varchar ),6) + Right(('00000'+Ltrim(str(@maxValue))),6)
-- Return the result of the function
RETURN @ResultVar
END
GO

--STEP3.Create dbo.autoIDTable Table use default constraint
Create table dbo.autoIDTable
(
autoid char(12) default dbo.GetNewAutoID(),
Data varchar(50)
);

-- STEP4.Try to select fucntion dbo.GetNewAutoID()
SELECT dbo.GetNewAutoID()

-- STEP5.Insert test data
insert into dbo.autoIDTable(Data) values('haha')
insert into dbo.autoIDTable(Data) values('wawa')
insert into dbo.autoIDTable(Data) values('gaga')

-- STEP6.select dbo.autoIDTable
select * from dbo.autoIDTable

-- STEP7.drop test database
USE master
GO
DROP DATABASE TESTDB
GO