生成订单(当前日期+0001)
create database DBDemo use DBDemo create VIEW v_GetDate AS SELECT dt=CONVERT(varchar(8),GETDATE(),112) GO --得到新编号的函数 create FUNCTION f_NextBH() RETURNS varchar(12) AS BEGIN DECLARE @dt varchar(8) SELECT @dt=dt FROM v_GetDate RETURN( SELECT @dt+RIGHT(10001+ISNULL(RIGHT(MAX(Id),4),0),4) FROM Orders WITH(XLOCK,PAGLOCK) WHERE Id like @dt+'%') END GO --在表中应用函数 CREATE TABLE Orders( Id char(12) PRIMARY KEY DEFAULT dbo.f_NextBH(), col int) go go --插入资料 INSERT Orders(col) VALUES(1) INSERT Orders(col) VALUES(2) INSERT Orders(col) VALUES(3) INSERT Orders(col) VALUES(5) --显示结果 SELECT * FROM Orders /*--结果 Id col ------------ ----------- 201301280001 1 201301280002 2 201301280003 3 201301280004 5 --*/