--
建表语句
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
乔栋 阅读(424)
评论(0) 编辑 收藏 引用 所属分类:
数据库