create procedure getDH @newdh output
As
declare @dh varchar(16)
Select @dh = 单号 from 单号表
declare @rq varchar(8)
set @rq = right(convert(varchar(10),getdate(),120),8)if left(@dh,13) = 'FUWU-'+@rq
set @newdh = left(@dh,14) + right('00'+ convert(varchar(2),convert(int,right(@dh,2)+1)),2)
else
set @newdh = 'FUWU-' + @rq + '-01'
update 单号表 set 单号 = @dh
go/********* usage:
declare @dh varchar(16)
exec getDH,@dh output
select @dh
*******************/
As
declare @dh varchar(16)
Select @dh = 单号 from 单号表
declare @rq varchar(8)
set @rq = right(convert(varchar(10),getdate(),120),8)if left(@dh,13) = 'FUWU-'+@rq
set @newdh = left(@dh,14) + right('00'+ convert(varchar(2),convert(int,right(@dh,2)+1)),2)
else
set @newdh = 'FUWU-' + @rq + '-01'
update 单号表 set 单号 = @dh
go/********* usage:
declare @dh varchar(16)
exec getDH,@dh output
select @dh
*******************/
(@my_head char(2),@no_index char(2),@lj_no varchar(15) output)
AS
begin
declare @sqlstring varchar(100),@year char(4),@month varchar(3),@day varchar(3),@tempstring varchar(15)
declare @LEN int,@count int,@count1 int
declare @beginstring varchar(15)
declare @number int
-------------------------------------------------------------------------
set @year=str(year(getdate()),4)
set @month='0'+ltrim(str(month(getdate()),2))
set @len=len(@month)
set @month=substring(@month,@len-1,2)
set @day='0'+ltrim(str(day(getdate()),2))
set @len=len(@day)
set @day=substring(@day,@len-1,2)
if @no_index='日'
set @tempstring=UPPER(rtrim(@my_head))+@year+@month+@day+'-'
else if @no_index='月'
set @tempstring=UPPER(rtrim(@my_head))+@year+@month+'-'
else if @no_index='年'
set @tempstring=UPPER(rtrim(@my_head))+@year+'-'
select @count=count(*) from 你的表名称 where Rec_no like @tempstring+'%'--今天
set @number=@count
set @beginstring=@tempstring
set @count=@count+1
set @tempstring=@beginstring+substring(str((10000+@count),5),2,5)
while @number>0
begin
if @count>9999 set @count=1
set @tempstring=@beginstring+substring(str((10000+@count),5),2,5)
select @number=(select count(*) from 你的表名称 where Rec_no=@tempstring)
set @count=@count+1
end
insert into 你的表名称(Rec_no) values(@tempstring)
end
as
select getdate() [output]
gocreate function getNumber()
returns char(16)
as
begin
declare @a char(16)
select @a='FUWU-'+right(CONVERT(varchar(10),(select [output] from v_getdate),120),8)+right(101+count(*),2) from 表 where datediff(day,substring(列,6,8),(select [output] from v_getdate))=0
return @a
end
go
放到表的默认值里:
dbo.f_getdate()或在过程里调用:
select dbo.f_getdate()
as
select getdate() [output]
gocreate function getNumber()
returns char(16)
as
begin
declare @a char(16)
select @a='FUWU-'+right(CONVERT(varchar(10),(select [output] from v_getdate),120),8)+right(101+count(*),2) from 表 where datediff(day,substring(列,6,8),(select [output] from v_getdate))=0
return @a
end
go
放到表的默认值里:
dbo.f_getdate()或在过程里调用:
select dbo.f_getdate()