SQL Server 用存储过程生成SN 流水号

---存储过程生成SN
CREATE PROCEDURE [dbo].[ReleaseSN]
    @CurrentSeqNo varchar(6)
AS
-------------------------------------------------------------------------------------------------  
-----Created by Jock.Luo, 2024/09/05.
-----Purpose: 得到新的流水号，目前是按照34进制来计算的.
-----输入的参数为当前流水号，返回下一个流水号.
-------------------------------------------------------------------------------------------------  
Begin
	Declare @iNewSeqNo varchar(6)
	
	Declare @FormatString varchar(36)
	Set @FormatString='0123456789ABCDEFGHJKLMNPQRSTUVWXYZ'
	-------------------------------------------------------------------------------------------------  
	Declare @CurrentFirstCode char(1)
	Declare @CurrentSecondCode char(1)
	Declare @CurrentThirdCode char(1)
	Declare @CurrentFourthCode char(1)
	Declare @CurrentLastModCode char(1)
	Declare @CurrentLastCode char(1)
	-------------------------------------------------------------------------------------------------  
	Declare @NextFirstCode char(1)
	Declare @NextSecondCode char(1)
	Declare @NextThirdCode char(1)
	Declare @NextFourthCode char(1)
	Declare @NextLastModCode char(1)
	Declare @NextLastCode char(1)
	-------------------------------------------------------------------------------------------------  
	Set @CurrentFirstCode=Substring(@CurrentSeqNo,1,1)
	Set @CurrentSecondCode=Substring(@CurrentSeqNo,2,1)
	Set @CurrentThirdCode=Substring(@CurrentSeqNo,3,1)
	Set @CurrentFourthCode=Substring(@CurrentSeqNo,4,1)
	Set @CurrentLastModCode=Substring(@CurrentSeqNo,5,1)
	Set @CurrentLastCode=Substring(@CurrentSeqNo,6,1)
	-------------------------------------------------------------------------------------------------
	if len(@CurrentSeqNo)=6
	begin
		If @CurrentSeqNo='ZZZZZZ'   -----@CurrentFirstCode+@CurrentSecondCode+@CurrentThirdCode
		Begin
			Set @iNewSeqNo='000000'
			goto resl
		End
		-------------------------------------------------------------------------------------------------  
		If @CurrentSecondCode+@CurrentThirdCode+@CurrentFourthCode+@CurrentLastModCode+@CurrentLastCode='ZZZZZ'
		Begin
			Set @NextFirstCode=Substring(@FormatString,CharIndex(@CurrentFirstCode,@FormatString)+1,1)
			Set @iNewSeqNo=@NextFirstCode+'00000'
			goto resl
		End
	
		-------------------------------------------------------------------------------------------------  
		If @CurrentThirdCode+@CurrentFourthCode+@CurrentLastModCode+@CurrentLastCode='ZZZZ'
		Begin
			Set @NextSecondCode=Substring(@FormatString,CharIndex(@CurrentSecondCode,@FormatString)+1,1)
			Set @iNewSeqNo=@CurrentFirstCode+@NextSecondCode+'0000'
			goto resl
		End

		-------------------------------------------------------------------------------------------------  
		If @CurrentFourthCode+@CurrentLastModCode+@CurrentLastCode='ZZZ'
		Begin
			Set @NextThirdCode=Substring(@FormatString,CharIndex(@CurrentThirdCode,@FormatString)+1,1)
			Set @iNewSeqNo=@CurrentFirstCode+@CurrentSecondCode+@NextThirdCode+'000'
			goto resl
		End

		-------------------------------------------------------------------------------------------------  
		If @CurrentLastModCode+@CurrentLastCode='ZZ'
		Begin
			Set @NextFourthCode=Substring(@FormatString,CharIndex(@CurrentFourthCode,@FormatString)+1,1)
			Set @iNewSeqNo=@CurrentFirstCode+@CurrentSecondCode+@CurrentThirdCode+@NextFourthCode+'00'
			goto resl
		End

		-------------------------------------------------------------------------------------------------  
		If +@CurrentLastCode='Z'
		Begin
			Set @NextLastModCode=Substring(@FormatString,CharIndex(@CurrentLastModCode,@FormatString)+1,1)
			Set @iNewSeqNo=@CurrentFirstCode+@CurrentSecondCode+@CurrentThirdCode+@CurrentFourthCode+@NextLastModCode+'0'
			goto resl
		End
		-------------------------------------------------------------------------------------------------  
		-----除上述情况之外的其它情况.
		Set @NextLastCode=Substring(@FormatString,CharIndex(@CurrentLastCode,@FormatString)+1,1)
		Set @iNewSeqNo=@CurrentFirstCode+@CurrentSecondCode+@CurrentThirdCode+@CurrentFourthCode+@CurrentLastModCode+@NextLastCode
		goto resl
		-------------------------------------------------------------------------------------------------
	end

	-------5位sn
	if len(@CurrentSeqNo)=5
	begin
		If @CurrentSeqNo='ZZZZZ'   -----@CurrentFirstCode+@CurrentSecondCode+@CurrentThirdCode
		Begin
			Set @iNewSeqNo='00000'
			goto resl
		End
		-------------------------------------------------------------------------------------------------  
		If @CurrentSecondCode+@CurrentThirdCode+@CurrentFourthCode+@CurrentLastModCode='ZZZZ'
		Begin
			Set @NextFirstCode=Substring(@FormatString,CharIndex(@CurrentFirstCode,@FormatString)+1,1)
			Set @iNewSeqNo=@NextFirstCode+'0000'
			goto resl
		End
	
		-------------------------------------------------------------------------------------------------  
		If @CurrentThirdCode+@CurrentFourthCode+@CurrentLastModCode='ZZZ'
		Begin
			Set @NextSecondCode=Substring(@FormatString,CharIndex(@CurrentSecondCode,@FormatString)+1,1)
			Set @iNewSeqNo=@CurrentFirstCode+@NextSecondCode+'000'
			goto resl
		End

		-------------------------------------------------------------------------------------------------  
		If @CurrentFourthCode+@CurrentLastModCode='ZZ'
		Begin
			Set @NextThirdCode=Substring(@FormatString,CharIndex(@CurrentThirdCode,@FormatString)+1,1)
			Set @iNewSeqNo=@CurrentFirstCode+@CurrentSecondCode+@NextThirdCode+'00'
			goto resl
		End

		-------------------------------------------------------------------------------------------------  
		If @CurrentLastModCode='Z'
		Begin
			Set @NextFourthCode=Substring(@FormatString,CharIndex(@CurrentFourthCode,@FormatString)+1,1)
			Set @iNewSeqNo=@CurrentFirstCode+@CurrentSecondCode+@CurrentThirdCode+@NextFourthCode+'0'
			goto resl
		End
		-------------------------------------------------------------------------------------------------  
		-----除上述情况之外的其它情况.
		Set @NextLastModCode=Substring(@FormatString,CharIndex(@CurrentLastModCode,@FormatString)+1,1)
		Set @iNewSeqNo=@CurrentFirstCode+@CurrentSecondCode+@CurrentThirdCode+@CurrentFourthCode+@NextLastModCode
		goto resl
		-------------------------------------------------------------------------------------------------
	end
	-------4位sn
	if len(@CurrentSeqNo)=4
	begin
		If @CurrentSeqNo='ZZZZ'
		Begin
			Set @iNewSeqNo='0000'
			goto resl
		End
		-------------------------------------------------------------------------------------------------  
		If @CurrentSecondCode+@CurrentThirdCode+@CurrentFourthCode='ZZZ'
		--@CurrentFirstCode+@CurrentSecondCode+@CurrentThirdCode+@CurrentFourthCode
		Begin
			Set @NextFirstCode=Substring(@FormatString,CharIndex(@CurrentFirstCode,@FormatString)+1,1)
			Set @iNewSeqNo=@NextFirstCode+'000'
			goto resl
		End
	
		-------------------------------------------------------------------------------------------------  
		If @CurrentThirdCode+@CurrentFourthCode='ZZ'
		Begin
			Set @NextSecondCode=Substring(@FormatString,CharIndex(@CurrentSecondCode,@FormatString)+1,1)
			Set @iNewSeqNo=@CurrentFirstCode+@NextSecondCode+'00'
			goto resl
		End

		-------------------------------------------------------------------------------------------------  
		If @CurrentFourthCode='Z'
		Begin
			Set @NextThirdCode=Substring(@FormatString,CharIndex(@CurrentThirdCode,@FormatString)+1,1)
			Set @iNewSeqNo=@CurrentFirstCode+@CurrentSecondCode+@NextThirdCode+'0'
			goto resl
		End
		-------------------------------------------------------------------------------------------------  
		-----除上述情况之外的其它情况.
		Set @NextFourthCode=Substring(@FormatString,CharIndex(@CurrentFourthCode,@FormatString)+1,1)
		Set @iNewSeqNo=@CurrentFirstCode+@CurrentSecondCode+@CurrentThirdCode+@NextFourthCode
		goto resl
		-------------------------------------------------------------------------------------------------
	end

	-------3位sn
	if len(@CurrentSeqNo)=3
	begin
		If @CurrentSeqNo='ZZZ'   -----@CurrentFirstCode+@CurrentSecondCode+@CurrentThirdCode
		Begin
			Set @iNewSeqNo='000'
			goto resl
		End
		-------------------------------------------------------------------------------------------------  
		If @CurrentSecondCode+@CurrentThirdCode='ZZ'
		Begin
			Set @NextFirstCode=Substring(@FormatString,CharIndex(@CurrentFirstCode,@FormatString)+1,1)
			Set @iNewSeqNo=@NextFirstCode+'00'
			goto resl
		End
	
		-------------------------------------------------------------------------------------------------  
		If @CurrentThirdCode='Z'
		Begin
			Set @NextSecondCode=Substring(@FormatString,CharIndex(@CurrentSecondCode,@FormatString)+1,1)
			Set @iNewSeqNo=@NextSecondCode+'0'
			goto resl
		End
		-------------------------------------------------------------------------------------------------  
		-----除上述情况之外的其它情况.
		Set @NextThirdCode=Substring(@FormatString,CharIndex(@CurrentThirdCode,@FormatString)+1,1)
		Set @iNewSeqNo=@CurrentFirstCode+@CurrentSecondCode+@NextThirdCode
		goto resl
		-------------------------------------------------------------------------------------------------
	end

	-------2位sn
	if len(@CurrentSeqNo)=2
	begin
		If @CurrentSeqNo='ZZ'   -----@CurrentFirstCode+@CurrentSecondCode+@CurrentThirdCode
		Begin
			Set @iNewSeqNo='00'
			goto resl
		End
		-------------------------------------------------------------------------------------------------  
		If @CurrentSecondCode='Z'
		Begin
			Set @NextFirstCode=Substring(@FormatString,CharIndex(@CurrentFirstCode,@FormatString)+1,1)
			Set @iNewSeqNo=@NextFirstCode+'0'
			goto resl
		End
		-------------------------------------------------------------------------------------------------  
		-----除上述情况之外的其它情况.
		Set @NextSecondCode=Substring(@FormatString,CharIndex(@CurrentSecondCode,@FormatString)+1,1)
		Set @iNewSeqNo=@CurrentFirstCode+@NextSecondCode
		goto resl
		-------------------------------------------------------------------------------------------------
	end

	-------1位sn
	if len(@CurrentSeqNo)=1
	begin
		If @CurrentSeqNo='Z'   -----@CurrentFirstCode+@CurrentSecondCode+@CurrentThirdCode
		Begin
			Set @iNewSeqNo='0'
			goto resl
		End
		-------------------------------------------------------------------------------------------------  
		If @CurrentSeqNo<>'Z'   -----@CurrentFirstCode+@CurrentSecondCode+@CurrentThirdCode
		Begin
			Set @NextFirstCode=Substring(@FormatString,CharIndex(@CurrentFirstCode,@FormatString)+1,1)
			Set @iNewSeqNo=@NextFirstCode
			goto resl
		End
		goto resl
		-------------------------------------------------------------------------------------------------
	end
	goto resl
resl:
	select(@iNewSeqNo)
End