create function AddNid(@date datetime)
returns varchar(12)
begin
declare @DT varchar(8)
declare @nid varchar(12)
declare @nidnew varchar(3)
declare @result varchar(12)
set @nid=null
set @DT=convert(varchar(8),@date,112)
select @nid=max(Nid) from News
if (@nid is null)
begin
set @result=ltrim(rtrim(@DT))+'000'
return @result
end
else
begin
set @nidnew=right(ltrim(rtrim(cast((1000+cast(right(@nid,3) as int)+1) as varchar(4)))),3)
set @result=ltrim(rtrim(@DT))+@nidnew
end
return @result
end
这是我自己写着用的,跟你的要求一模一样,只差后面边个N,自己连上吧
下面的是我现在写的,对sql语句不是很熟悉,写得很烦所,而且下面的执行有错误,当一个订单里有字母时,就出现错误,查询后面加的日期条件好像没有,这是为什么。
if exists(select name from sysObjects
where name='OrderID' and type='P')
drop proc OrderID
go
create proc OrderID
@id varchar(50) output
as
declare @dataStr varchar(50),@IdMax varchar(50),@n int
set @dataStr=DATENAME(yy,GETDATE())
set @dataStr=@dataStr+datename(m,getdate())
set @dataStr=@dataStr+datename(d,getdate())
if exists(select max(OrderID) from OperationMain where OrderID=@dataStr)
begin
select @IdMax=max(OrderID) from OperationMain where OrderID=@dataStr
set @dataStr=@dataStr+cast(cast(RIGHT(@IdMax,3) as int)+1 as varchar(50))
set @id=@dataStr
end
else
begin
set @id=@dataStr+'1'
end