一个创建流水编号的存储过程

**创建流水编号格式为
yyyy-MM-dd
年月日后面的流水编码会根据 第二天的日期重新生成
**

存储过程代码

USE [after_sale_test2]
GO
/****** Object:  StoredProcedure [dbo].[SDP_GetNewBillNo]    Script Date: 2021/10/18 11:23:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SDP_GetNewBillNo]
	@dhmc varchar(50),
	@count int
AS
BEGIN 
	SET NOCOUNT ON;
	declare @yyyymmdd varchar(50)
	declare @ls_head_tag_ex varchar(50), @ls_head_tag varchar(50), @li_date_len int,@ls_center_tag_ex varchar(50), @ls_center_tag varchar(50), 
				@li_number_len int,@ls_tail_tag_ex varchar(50), @ls_tail_tag varchar(50),@ls_old_dh varchar(50),@ls_fullzero varchar(30),
				@ls_number varchar(50),@newBill varchar(50),@ls_olddh_ny varchar(50),@li_first int
	select @yyyymmdd = CONVERT(varchar(50),year(getdate()))+right('00'+CONVERT(varchar(50),month(getdate())),2)+right('00'+CONVERT(varchar(50),day(getdate())),2)
	
	SELECT @ls_head_tag_ex = head_tag_ex,@ls_head_tag = head_tag, 
		  @li_date_len = date_len, @ls_center_tag_ex = center_tag_ex,
		  @ls_center_tag = center_tag,@li_number_len = number_len,
		  @ls_tail_tag_ex = tail_tag_ex,@ls_tail_tag = tail_tag 
	FROM dict_new_dh 
	Where dict_new_dh.dhmc = @dhmc	
	 
	SELECT @ls_old_dh = dict_new_dh.old_dh 
	FROM dict_new_dh 
	Where dhmc = @dhmc  
			
	declare @i int
	select @i =0,@ls_fullzero=''
	while @i<=@li_number_len
	begin
		set @ls_fullzero = @ls_fullzero+'0'
		set @i = @i+1
	End
	if @li_date_len=2
		Set @yyyymmdd = SUBSTRING(@yyyymmdd,3,2)
	else if @li_date_len=6
		Set @yyyymmdd = SUBSTRING(@yyyymmdd,1,6)
	else if @li_date_len =22
	begin
    set	@yyyymmdd = SUBSTRING(@yyyymmdd,3,4)
		set @li_date_len = 4
		end
	else if @li_date_len=222
	Begin
		Set @yyyymmdd = right(@yyyymmdd,6)
		set @li_date_len=6
	End
	else  
		Set @yyyymmdd = SUBSTRING(@yyyymmdd,1,@li_date_len)
	
	if @ls_head_tag_ex='L'
		Set @ls_head_tag =  @ls_head_tag
	else if @ls_head_tag_ex='R'
		Set @ls_head_tag = @ls_head_tag 
	 
		
	if @ls_center_tag_ex='L'
		Set @ls_center_tag =  @ls_center_tag
	else if @ls_center_tag_ex='R'
		Set @ls_center_tag = @ls_center_tag
	 
		
	if @ls_tail_tag_ex='L'
		Set @ls_tail_tag =  @ls_tail_tag
	else if @ls_tail_tag_ex='R'
		Set @ls_tail_tag = @ls_tail_tag
	 
	if @ls_old_dh=''
	begin	
		set @li_first = 1
		set @ls_number = RIGHT(@ls_fullzero+'1',@li_number_len)
		set @newBill = @ls_head_tag + @yyyymmdd + @ls_center_tag + @ls_number + @ls_tail_tag
	End
	else
	begin
		set @ls_olddh_ny  = SUBSTRING(@ls_old_dh,LEN(@ls_head_tag)+1,@li_date_len)
		 
		if @ls_olddh_ny <> @yyyymmdd
		begin
			set @li_first = 1
			set @ls_number = RIGHT(@ls_fullzero+'1',@li_number_len)
			set @newBill = @ls_head_tag + @yyyymmdd + @ls_center_tag + @ls_number + @ls_tail_tag
		end
		else
		begin
			declare @li_len int
			set @li_len = Len(@ls_head_tag) + @li_date_len + Len(@ls_center_tag) + 1
			set @ls_number = SUBSTRING(@ls_old_dh,@li_len, @li_number_len)
			set @li_first = CONVERT(int,@ls_number)+1
			set @ls_number = Right(@ls_fullzero + convert(varchar(50),@li_first) , @li_number_len)
			set @newBill = @ls_head_tag + @yyyymmdd + @ls_center_tag + @ls_number + @ls_tail_tag
		end
	end
	create table #bills ( billno varchar(50))
	insert into #bills values (@newBill)
	declare @k int
	set @k = 2
	while @k <=@count
	begin
		set @k = @k+1
		set @li_first = @li_first + 1
		set @ls_number = Right(@ls_fullzero + convert(varchar(50),@li_first) , @li_number_len)
		insert into #bills values (@ls_head_tag + @yyyymmdd + @ls_center_tag + @ls_number + @ls_tail_tag)
	end
	update dict_new_dh
	set old_dh = isnull((select MAX(billno) from #bills),old_dh)
	where dhmc = @dhmc
	select * from #bills
END

建立一个表格可以随意变换样式

总结

项目中有使用到的方法,一起分享给大家, 有更好的方法也可以大家一起分享