create table tb
(id int identity,
name varchar(10),
code as 'BH'+right('0000'+cast(id as varchar),5))
go
insert tb(name) select 'A'
union all select 'B'
union all select 'C'
union all select 'D'select * from tbdrop table tb/*
id name code
----------- ---------- ------------
1 A BH00001
2 B BH00002
3 C BH00003
4 D BH00004(所影响的行数为 4 行)
*/
(id int identity,
name varchar(10),
code as 'BH'+right('0000'+cast(id as varchar),5))
go
insert tb(name) select 'A'
union all select 'B'
union all select 'C'
union all select 'D'select * from tbdrop table tb/*
id name code
----------- ---------- ------------
1 A BH00001
2 B BH00002
3 C BH00003
4 D BH00004(所影响的行数为 4 行)
*/
insert tb select 'a'
union all select 'b'select * from tbdrop table tbid id2 col
----------- ------------ ----------
1 BH00001 a
2 BH00002 b(2 行受影响)
--得到新编号的函数
CREATE FUNCTION f_NextBH()
RETURNS char(8)
AS
BEGIN
RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK))
END
GO--在表中应用函数
CREATE TABLE tb(
BH char(8) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)--插入资料
BEGIN TRAN
INSERT tb(col) VALUES(1)
INSERT tb(col) VALUES(2)
INSERT tb(col) VALUES(3)
DELETE tb WHERE col=3
INSERT tb(col) VALUES(4)
INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14)
COMMIT TRAN--显示结果
SELECT * FROM tb
/*--结果
BH col
---------------- -----------
BH000001 1
BH000002 2
BH000003 4
BH000004 14
--*/
gocreate proc my_proc
as
begin
declare @code as varchar(10)
set @code = (select max(code) from tb)
if @code is null
set @code = 'bh00001'
else
set @code = 'bh' + right('0000'+cast(cast(right(@code,5) as int) + 1 as varchar),5)
insert into tb values(@code)
end
go--1
exec my_proc
select * from tb
/*
code
----------
bh00001(所影响的行数为 1 行)
*/--2
exec my_proc
select * from tb
/*
code
----------
bh00001
bh00002(所影响的行数为 2 行)
*/drop table tb
drop proc my_proc
--得到新编号的函数
CREATE FUNCTION f_NextBH()
returns varchar(7) --类别
as
begin
return (select 'BH'+right('00000'+ltrim(isnull(max(right(BH,5)),0)+1),5) from tb
with (XLOCK,PAGLOCK))
end
GO--在表中应用函数
CREATE TABLE tb(
BH varchar(7) PRIMARY KEY DEFAULT dbo.f_NextBH())
INSERT tb(BH) VALUES(dbo.f_NextBH())
INSERT tb(BH) VALUES(dbo.f_NextBH())
INSERT tb(BH) VALUES(dbo.f_NextBH())
INSERT tb(BH) VALUES(dbo.f_NextBH())
--显示结果
SELECT * FROM tb
--创建得到当前日期的视图
CREATE VIEW v_GetDate
AS
SELECT dt=CONVERT(CHAR(6),GETDATE(),12)
GO--得到新编号的函数
CREATE FUNCTION f_NextBH()
RETURNS char(12)
AS
BEGIN
DECLARE @dt CHAR(6)
SELECT @dt=dt FROM v_GetDate
RETURN(
SELECT @dt+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6)
FROM tb WITH(XLOCK,PAGLOCK)
WHERE BH like @dt+'%')
END
GO--在表中应用函数
CREATE TABLE tb(
BH char(12) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)--插入资料
INSERT tb(col) VALUES(1)
INSERT tb(col) VALUES(2)
INSERT tb(col) VALUES(3)
DELETE tb WHERE col=3
INSERT tb(col) VALUES(4)
INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14)--显示结果
SELECT * FROM tb
/*--结果
BH col
------------------- -----------
050405000001 1
050405000002 2
050405000003 4
050405000004 14
--*/ 流水号