create function getgcode() returns varchar(20)
as
begin
declare @time varchar(20)
declare @num varchar(3)='001'
declare @lastnum int
set @time=CONVERT(varchar(20),GETDATE(),120)
set @time=REPLACE(@time,'-','')
set @time=REPLACE(@time,':','')
set @time=REPLACE(@time,' ','')
if exists(select gcode from tbProduct where gcode like(@time+'%'))
begin
set @lastnum=convert(int,substring((select top 1 gcode from tbProduct where gcode like(@time+'%')order by gcode desc),15,3))+1
if(@lastnum<10)
set @num='00'+CONVERT(varchar(4),@lastnum)
else if(@lastnum<100)
set @num='0'+CONVERT(varchar(4),@lastnum)
else if(@lastnum<1000)
set @num=CONVERT(varchar(4),@lastnum)
set @time=@time+@num
end
else
begin
set @time=@time+@num
end
return @time
end
