--
建表語句
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下測試成功
posted on 2007-03-06 13:38
喬棟 閱讀(438)
評論(0) 編輯 收藏 引用 所屬分類:
數據庫