sql存储过程 生成格式年月日+0001格式的流水账号

if exists(select * from sysobjects where name='proc_Tickets')
drop proc proc_Tickets
go
--定义一个获取时间的变量，一个获取表中数据的变量
create proc proc_Tickets
@carId int
as
declare @s nvarchar(50),@No bigint,@sum nvarchar(50) ----定义三个参数1.时间2.流水号3.当天的流水号的总数
set @s=convert(char(8),getdate(),112)
select @sum=count(*) from Tickets where left(TicketsNo,8)=convert(char(8),getdate(),112) and carId=@carId
select top 1 @NO=TicketsNo from Tickets where carId=@carId order by TicketsNo desc
if(left(@NO,8)=@s)
begin
insert into Tickets values(@carId,cast((cast(@NO as bigint)+1) as varchar(50)))
end
else
begin
if(cast(@sum as int)=0) ---如果今天不存在账号时，加一个号码
begin
insert into Tickets values(@carId,@s+'0001')
end
else --存在的话就取最后一个流水号加一，用于手动添加不同日期数据的操作，实际使用时不需要
begin
insert into Tickets values(@carId,cast((cast(@NO as bigint)+1) as varchar(50)))
end
end
select top 1 * from Tickets where carId=@carId order by TicketsNo desc --查询刚刚产生的流水号码
go


exec proc_Tickets 1