SQL主鍵:日期+序列

CREATE FUNCTION f_Next()
RETURNS char(12)
AS
BEGIN
    DECLARE @now varchar(8)
    DECLARE @NEW_ID bigint
    DECLARE @id varchar(12)
    set @now =  CONVERT(varchar(8), GETDATE(), 112)
    SET @NEW_ID = NULL
    SELECT top 1 @NEW_ID = CONVERT(bigint,List_No)+1 FROM [Te_List] WHERE List_No LIKE @now+'%' 
    order by List_No DESC
    if (@NEW_ID is NULL)
    set @id = @now + '0001'
    else   
    set @id = CONVERT(varchar(12),@NEW_ID)
    return @id
END
GO

简单思想 就是先找出当天最大的序号加一 再保存就OK

之前的不通用,利用动态语句修改一下让它更通用

CREATE FUNCTION f_Next
(@table_name varchar(20),@idName varchar(20))
RETURNS char(12)
AS
BEGIN
    DECLARE @now varchar(8)
    DECLARE @NEW_ID bigint
    DECLARE @id varchar(12)
    DECLARE @sql nvarchar(300)
    set @now =  CONVERT(varchar(8), GETDATE(), 112)
    SET @NEW_ID = NULL
        set @now =  CONVERT(varchar(8), GETDATE(), 112)
    SET @sql = 'SELECT top 1 @NEW_ID=CONVERT(bigint,'+@idName+')+1 from '+@table_name+
           ' where '+@idName+'LIKE ' + @now + '% order by '+@idName+' DESC'
    exec sp_executesql @sql,N'@NEW_ID AS bigint OUTPUT',@NEW_ID out
    if (@NEW_ID is NULL)
    set @id = @now + '0001'
    else   
    set @id = CONVERT(varchar(12),@NEW_ID)
    return @id
END
GO