--创建视图,得到当前日期(因为函数中不能使用getdate())
create view ps_number as select dt='YXT-'+convert(varchar,getdate(),112)
go
--创建得到最大id的函数
create function ps_pxu()
returns varchar(16)
as
begin
declare @pxu varchar(16),@dt varchar(14)
select @dt=dt from ps_number
select @pxu=@dt+'-'+right(1001+isnull(right(max(pxu),3),0),3)
from ps_list where pxu like @dt+'-%'
return(@pxu)
end
go
YXT-20100128-001
YXT-20100128-002
YXT-20100129-001
每天重1开始 谁知道怎么改成每年重1开始了
插入时用的存储过程
insert into ps_list(pdate,pname,pdao,pqu,pfa,pfatel,pshou,pshoutel,pfar,psongr) values(@pdate,@pname,@pdao,@pqu,@pfa,@pfatel,@pshou,@pshoutel,@pfar,@psongr)
create view ps_number as select dt='YXT-'+convert(varchar,getdate(),112)
go
--创建得到最大id的函数
create function ps_pxu()
returns varchar(16)
as
begin
declare @pxu varchar(16),@dt varchar(14)
select @dt=dt from ps_number
select @pxu=@dt+'-'+right(1001+isnull(right(max(pxu),3),0),3)
from ps_list where pxu like @dt+'-%'
return(@pxu)
end
go
YXT-20100128-001
YXT-20100128-002
YXT-20100129-001
每天重1开始 谁知道怎么改成每年重1开始了
插入时用的存储过程
insert into ps_list(pdate,pname,pdao,pqu,pfa,pfatel,pshou,pshoutel,pfar,psongr) values(@pdate,@pname,@pdao,@pqu,@pfa,@pfatel,@pshou,@pshoutel,@pfar,@psongr)
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER proc [dbo].[AddList]
@pdate varchar(30),
@pname varchar(50),
@pdao varchar(30),
@pqu varchar(30),
@pfa varchar(50),
@pfatel varchar(30),
@pshou varchar(50),
@pshoutel varchar(30),
@pfar varchar(20),
@psongr varchar(20)
asinsert into ps_list(pdate,pname,pdao,pqu,pfa,pfatel,pshou,pshoutel,pfar,psongr) values(@pdate,@pname,@pdao,@pqu,@pfa,@pfatel,@pshou,@pshoutel,@pfar,@psongr)
set @IndexYear=Substring(convert(varchar,getdate(),102),3,2) 年 SELECT @IndexNum=isnull(max(IndexNum),0)+1
FROM table
where
--格式化NUM号
SET @FormatIndexNum = RIGHT('00000' + cast(@IndexNum as nvarchar),5) 流水号= @IndexYear + '流水号' + @FormatIndexNum
set @IndexYear=Substring(convert(varchar,getdate(),102),3,2) 年 SELECT @IndexNum=isnull(max(IndexNum),0)+1
FROM table
where
--格式化NUM号
SET @FormatIndexNum = RIGHT('00000' + cast(@IndexNum as nvarchar),5) 流水号= @IndexYear + '流水号' + @FormatIndexNum