create table T(ID char(8) primary key,Name nvarchar(50)) go if object_id('P_test') is not null drop proc P_test go create proc P_test(@Date datetime,@ID char(8) output) as select @ID=convert(char(4),@Date,12)+right(10001+isnull(max(right(cast(ID as bigint),4)),0),4) from T where left(ID,4)=convert(char(4),@Date,12)go declare @ID char(8) exec P_test '2008-06-02',@ID output select @ID /*-------- 08060001(1 個資料列受到影響) */
--用来自定义函数里获取当天的日期 CREATE VIEW VB_GetDate AS SELECT DT=CONVERT(VARCHAR(4),GETDATE(),12) GO--得到新编号的函数CREATE FUNCTION F_NextIDNO() RETURNS VARCHAR(10) AS BEGIN DECLARE @DT VARCHAR(4) SELECT @DT=DT FROM VB_GetDate RETURN( SELECT @DT+RIGHT(10001+ISNULL(RIGHT(MAX(IDNO),4),0),4) FROM TB WITH(XLOCK,PAGLOCK) WHERE IDNO like @DT+'%') END GOCREATE TABLE TB( IDNO VARCHAR(10) PRIMARY KEY DEFAULT dbo.f_NextIDNO(), COL int)--测试插入资料 INSERT tb(col) VALUES(1) INSERT tb(col) VALUES(2) INSERT tb(col) VALUES(3) INSERT tb(col) VALUES(4)SELECT * FROM TB--结果 /* IDNO COL ---------- ----------- 08060001 1 08060002 2 08060003 3 08060004 4(所影响的行数为 4 行) */
结合前面的例子和这个,自己在修改一下就可以了. --某一列中最小不重复的整数 declare @t table(id int) insert into @t select 1 insert into @t select 4 insert into @t select 5 insert into @t select min(id)+1 from @t a where not exists(select 1 from @t where id=a.id+1) select * from @t
--创建得到当前日期的视图 CREATE VIEW v_GetDate AS SELECT dt=CONVERT(CHAR(4),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(0000+ISNULL(RIGHT(MAX(BH),4),0),4) FROM tb WITH(XLOCK,PAGLOCK)) 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) --显示结果 SELECT * FROM tb
CREATE FUNCTION f_NextBH() RETURNS char(12) AS BEGIN DECLARE @dt CHAR(6),@r char(12) SELECT @dt=CONVERT(VARCHAR(4),GETDATE(),12) SELECT @r=@dt+RIGHT(10001+MIN(BH),4) FROM( SELECT BH=RIGHT(BH,6) FROM tb WITH(XLOCK,PAGLOCK) WHERE BH like @dt+'%' UNION ALL SELECT 0 )a WHERE NOT EXISTS( SELECT * FROM tb WITH(XLOCK,PAGLOCK) WHERE BH like @dt+'%' AND BH=@dt+RIGHT(10001+a.BH,4)) RETURN(@r) END GO 这个融合了补号处理
create table T(ID char(8) primary key,Name nvarchar(50))
go
if object_id('P_test') is not null
drop proc P_test
go
create proc P_test(@Date datetime,@ID char(8) output)
as
select
@ID=convert(char(4),@Date,12)+right(10001+isnull(max(right(cast(ID as bigint),4)),0),4)
from
T
where left(ID,4)=convert(char(4),@Date,12)go
declare @ID char(8)
exec P_test '2008-06-02',@ID output
select @ID
/*--------
08060001(1 個資料列受到影響)
*/
--用来自定义函数里获取当天的日期
CREATE VIEW VB_GetDate
AS
SELECT DT=CONVERT(VARCHAR(4),GETDATE(),12)
GO--得到新编号的函数CREATE FUNCTION F_NextIDNO()
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @DT VARCHAR(4)
SELECT @DT=DT FROM VB_GetDate
RETURN(
SELECT @DT+RIGHT(10001+ISNULL(RIGHT(MAX(IDNO),4),0),4)
FROM TB WITH(XLOCK,PAGLOCK)
WHERE IDNO like @DT+'%')
END
GOCREATE TABLE TB(
IDNO VARCHAR(10) PRIMARY KEY DEFAULT dbo.f_NextIDNO(),
COL int)--测试插入资料
INSERT tb(col) VALUES(1)
INSERT tb(col) VALUES(2)
INSERT tb(col) VALUES(3)
INSERT tb(col) VALUES(4)SELECT * FROM TB--结果
/*
IDNO COL
---------- -----------
08060001 1
08060002 2
08060003 3
08060004 4(所影响的行数为 4 行)
*/
--某一列中最小不重复的整数
declare @t table(id int)
insert into @t select 1
insert into @t select 4
insert into @t select 5
insert into @t select min(id)+1 from @t a
where not exists(select 1 from @t where id=a.id+1)
select * from @t
CREATE VIEW v_GetDate
AS
SELECT dt=CONVERT(CHAR(4),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(0000+ISNULL(RIGHT(MAX(BH),4),0),4) FROM tb WITH(XLOCK,PAGLOCK))
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)
--显示结果
SELECT * FROM tb
RETURNS char(12)
AS
BEGIN
DECLARE @dt CHAR(6),@r char(12)
SELECT @dt=CONVERT(VARCHAR(4),GETDATE(),12) SELECT @r=@dt+RIGHT(10001+MIN(BH),4)
FROM(
SELECT BH=RIGHT(BH,6) FROM tb WITH(XLOCK,PAGLOCK)
WHERE BH like @dt+'%'
UNION ALL SELECT 0
)a WHERE NOT EXISTS(
SELECT * FROM tb WITH(XLOCK,PAGLOCK)
WHERE BH like @dt+'%'
AND BH=@dt+RIGHT(10001+a.BH,4))
RETURN(@r)
END
GO
这个融合了补号处理
但的确如楼上所式,如果该流水号出现并发的现象的话该怎么处理呢?
create table t(ID int dentity,YYMM char(4), Code as YYMM+right(10000+ID,4)
万分感谢了。
CREATE VIEW v_GetDate
AS
SELECT dt=CONVERT(CHAR(4),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(0000+ISNULL(RIGHT(MAX(BH),4),0),4) FROM tb WITH(XLOCK,PAGLOCK))
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)
--显示结果
SELECT * FROM tb谢谢pt1314917大哥的例子,不过该例子经调试后会出错,出错信息如下:
违反了 PRIMARY KEY 约束 'PK__tb__117F9D94'。不能在对象 'dbo.tb' 中插入重复键。
语句已终止。
出错语句在insert语句处.
--创建得到当前日期的视图
CREATE VIEW v_GetDate
AS
SELECT dt=CONVERT(varCHAR(4),GETDATE(),12)
GO--得到新编号的函数
CREATE FUNCTION f_NextBH()
RETURNS char(12)
AS
BEGIN
DECLARE @dt varCHAR(6)
SELECT @dt=dt FROM v_GetDate
RETURN(SELECT @dt+RIGHT('0000'+ltrim(ISNULL(MAX(RIGHT(BH,4)),0)+1),4) FROM tb WITH(XLOCK,PAGLOCK))
END
GO--在表中应用函数
CREATE TABLE tb(
BH varchar(8) 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)
--显示结果
SELECT * FROM tb