【SQL Server】SQL编号生成序列化

编号避免重复，且流水号自增

USE [TWEstate]
GO
/****** Object:  StoredProcedure [dbo].[PROC_Property_GetPropertyIntentionMoneyIdSYS_SEL]    Script Date: 2019/12/7 10:05:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author>
-- Create date: <2019/03/18>
-- Description:	<>
-- =============================================
ALTER PROCEDURE [dbo].[PROC_Property_GetPropertyIntentionMoneyIdSYS_SEL] 
    @cityCode	   			   VARCHAR(50)								   --房源所在市编码
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE
	@intentionMoneyIdYXJ					  VARCHAR(50) = 'Y'			   --意向（Y）  (Y+房源所在市（含直辖市）编码+年后两位+月+日+5位数编号（从00001始）)
	,@MAX_SEQ int = 99999
	,@current int
	,@result varchar(30)
	,@name varchar(20)
	,@sql nvarchar(500)
	--,@code_type varchar(1)
	,@message varchar(50)
	,@modify_date date
	,@today varchar(6)
	,@curentday varchar(6)
	,@days int
	
	set @name = 'Seq_IntentionMoneyId'
	-- 判断序列是否存在
	declare @count int;
	SELECT @count=count(name) FROM sys.sequences where name = @name
    if @count = 0
	begin
		set @sql = 'CREATE SEQUENCE [dbo].' + QUOTENAME(@name) + '
					 AS [int]
					 START WITH 1
					 INCREMENT BY 1
					 MINVALUE 0
					 MAXVALUE ' + cast(@MAX_SEQ*10 as varchar) + '
					 CYCLE 
					 CACHE'
		EXEC sp_executesql @sql
	end 


	SELECT @current=CAST(current_value as int),@curentday=substring(convert(varchar(8),modify_date,112),3,6),@today=substring(convert(varchar(8),getdate(),112),3,6) FROM sys.sequences WHERE name=@name
	
	if @today > @curentday
	begin
		-- 复位（从0开始）
		set @sql = 'alter SEQUENCE [dbo].' + QUOTENAME(@name) + ' RESTART'
		EXEC sp_executesql @sql
	end
	else
	begin
		print(@current)
		
		if @current >= @MAX_SEQ
		begin
			set @days = @current / @MAX_SEQ
			-- 每超越过最大位数时，日期加1天，占用明天的流水
			if @days > 0
				set @today = substring(convert(varchar(8),dateadd(day,@days,getdate()),112),3,6)
		end
	end

	-- 获取最新值
	set @sql = 'SELECT @current = NEXT VALUE FOR [dbo].' + QUOTENAME(@name)
	EXEC sp_executesql @sql,N'@current int OUT',@current = @current OUTPUT
	if @current > @MAX_SEQ
	begin
		-- 每超越过最大位数时，日期加1天，占用明天的流水
		set @current = @current - @days * @MAX_SEQ
	end


	set @result = @IntentionMoneyIdYXJ + @cityCode + @today + RIGHT('00000'+CONVERT(VARCHAR(5),@current),5)

	select @result AS intentionMoneyId

END