-- 建表語句
CREATE ? TABLE ? [ MyTable ] ?(
???? [ MyId ] ? [ varchar ] ?( 50 )?COLLATE?Chinese_PRC_CI_AS? NULL ?,
???? [ MyName ] ? [ varchar ] ?( 50 )?COLLATE?Chinese_PRC_CI_AS? NULL ?
)? ON ? [ PRIMARY ]
GO

-- 得到新的編號（不考慮補號）
create ? function ?f_NextID( @myDate ? datetime )
Returns ? char ( 12 )
as
begin ?
???? Declare ? @temp ? char ( 6 )
????? Declare ? @ret ? char ( 12 )
???? set ? @temp = Substring ( Convert ( char , @myDate ), 9 , 2 ) + Substring ( Convert ( char , @myDate ), 1 , 2 ) + Substring ( Convert ( char , @myDate ), 4 , 2 )
???? if ( @temp ? in ?( SELECT ? distinct ?myid = left (myid, 6 )? FROM ?mytable? WITH (XLOCK,PAGLOCK)))
???????????? Select ? @ret = @temp + right ( 1000001 + Isnull ( Max ( Right (MyID, 6 )), 0 ), 6 )? from ?Mytable? with (xlock,paglock)? where ? @temp = left (MyID, 6 )
???? else
???????? Select ? @ret = @temp + ' 000001 ' ? from ?Mytable? with (xlock,paglock)
???? return ? @ret
end ?
GO


drop ? table ?MyTable
-- 建表語句
CREATE ? TABLE ? [ MyTable ] ?(
???? [ MyId ] ? [ varchar ] ?( 50 )?COLLATE?Chinese_PRC_CI_AS? NULL ?,
???? [ MyName ] ? [ varchar ] ?( 50 )?COLLATE?Chinese_PRC_CI_AS? NULL ?
)? ON ? [ PRIMARY ]
GO

-- 得到新的編號（融合了補號處理）
create ? FUNCTION ?f_NextNewID( @myDate ? datetime )
RETURNS ? char ( 12 )
AS
BEGIN
???? Declare ? @temp ? char ( 6 )
????? Declare ? @ret ? char ( 12 )
???? set ? @temp = Substring ( Convert ( char , @myDate ), 9 , 2 ) + Substring ( Convert ( char , @myDate ), 1 , 2 ) + Substring ( Convert ( char , @myDate ), 4 , 2 )
???? DECLARE ? @r ? char ( 12 )
????
???? SELECT ? @r = @temp + RIGHT ( 1000001 + MIN (MyId), 6 )
???? FROM (
???????? SELECT ?MyId = RIGHT (MyId, 6 )? FROM ?MyTable? WITH (XLOCK,PAGLOCK)
???????? UNION ? ALL ? SELECT ? 0
????)a?
?? WHERE ? NOT ? EXISTS (
???????? SELECT ? * ? FROM ?MyTable?? WITH (XLOCK,PAGLOCK)
???????? WHERE ?MyId = @temp + RIGHT ( 1000001 + a.myID, 6 ))
???? RETURN ( @r )
END
GO


-- 測試語句(可改變時間測試一下）
insert ?MyTable? values ?(dbo.f_NextNewID( GetDate ()), ' one ' )
insert ?MyTable? values ?(dbo.f_NextNewID( GetDate ()), ' two ' )
insert ?MyTable? values ?(dbo.f_NextNewID( GetDate ()), ' three ' )
insert ?MyTable? values ?(dbo.f_NextNewID( GetDate ()), ' four ' )
insert ?MyTable? values ?(dbo.f_NextNewID( GetDate ()), ' five ' )
insert ?MyTable? values ?(dbo.f_NextNewID( GetDate ()), ' six ' )
Select ? * ? from ?MyTable? order ? by ?MyId
GO
delete ?MyTable? where ?myId = ' 060629000002 '
delete ?MyTable? where ?myId = ' 060629000005 '
Select ? * ? from ?MyTable? order ? by ?MyId
Go
insert ?MyTable? values ?(dbo.f_NextNewID( GetDate ()), ' nexttwo ' )
insert ?MyTable? values ?(dbo.f_NextNewID( GetDate ()), ' nextfive ' )
Select ? * ? from ?MyTable? order ? by ?MyId
GO



§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§

SQL下測試成功