SQL Server 存储过程生成Mac Address(前6码固定,后6码为生成)

ALTER PROCEDURE [dbo].[GetNextMacNo]
    @CurrentMacNo varchar(6)
AS
-------------------------------------------------------------------------------------------------  
-----Created by Jock.Luo, 2024/09/05.
-----Purpose: 得到新的流水号，目前是按照34进制来计算的.
-----输入的参数为当前流水号，返回下一个流水号.
-------------------------------------------------------------------------------------------------  
Begin
	Declare @macAddress varchar(18)
	Declare @OEMmacAddress varchar(18)
	Declare @FormatString varchar(36)
	Set @FormatString='0123456789ABCDEF'
	set @OEMmacAddress='A1B2C3'
	--A1-B2-C3 ：OUI给MAC制造商分配的地址段，每个厂商唯一。
	--D4-E5-F6 ：MAC制造商自己分配，理论上在地址段内唯一。
	-------------------------------------------------------------------------------------------------  
	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(@CurrentMacNo,1,1)
	Set @CurrentSecondCode=Substring(@CurrentMacNo,2,1)
	Set @CurrentThirdCode=Substring(@CurrentMacNo,3,1)
	Set @CurrentFourthCode=Substring(@CurrentMacNo,4,1)
	Set @CurrentLastModCode=Substring(@CurrentMacNo,5,1)
	Set @CurrentLastCode=Substring(@CurrentMacNo,6,1)
	-------------------------------------------------------------------------------------------------
	if len(@CurrentMacNo)=6
	begin
		If @CurrentMacNo='FFFFFF'   -----@CurrentFirstCode+@CurrentSecondCode+@CurrentThirdCode
		Begin
			Set @macAddress='000000'
			goto resl
		End
		-------------------------------------------------------------------------------------------------  
		If @CurrentSecondCode+@CurrentThirdCode+@CurrentFourthCode+@CurrentLastModCode+@CurrentLastCode='FFFFF'
		Begin
			Set @NextFirstCode=Substring(@FormatString,CharIndex(@CurrentFirstCode,@FormatString)+1,1)
			Set @macAddress=@NextFirstCode+'00000'
			goto resl
		End
	
		-------------------------------------------------------------------------------------------------  
		If @CurrentThirdCode+@CurrentFourthCode+@CurrentLastModCode+@CurrentLastCode='FFFF'
		Begin
			Set @NextSecondCode=Substring(@FormatString,CharIndex(@CurrentSecondCode,@FormatString)+1,1)
			Set @macAddress=@CurrentFirstCode+@NextSecondCode+'0000'
			goto resl
		End

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

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

		-------------------------------------------------------------------------------------------------  
		If +@CurrentLastCode='F'
		Begin
			Set @NextLastModCode=Substring(@FormatString,CharIndex(@CurrentLastModCode,@FormatString)+1,1)
			Set @macAddress=@CurrentFirstCode+@CurrentSecondCode+@CurrentThirdCode+@CurrentFourthCode+@NextLastModCode+'0'
			goto resl
		End
		-------------------------------------------------------------------------------------------------  
		-----除上述情况之外的其它情况.
		Set @NextLastCode=Substring(@FormatString,CharIndex(@CurrentLastCode,@FormatString)+1,1)
		Set @macAddress=@CurrentFirstCode+@CurrentSecondCode+@CurrentThirdCode+@CurrentFourthCode+@CurrentLastModCode+@NextLastCode
		goto resl
		-------------------------------------------------------------------------------------------------
	end
	goto resl
resl:
	set @macAddress=@OEMmacAddress+@macAddress
	---下面格式化为:A1-B2-C3-D4-E5-F6
	SET @macAddress = SUBSTRING(@macAddress, 1, 2) + '-' + SUBSTRING(@macAddress, 3, 2)+ '-' + SUBSTRING(@macAddress, 5, 2) + '-' + SUBSTRING(@macAddress, 7, 2) + '-' + SUBSTRING(@macAddress, 9, 2) + '-' + SUBSTRING(@macAddress, 11, 2)
	select(@macAddress)
End