sqlserver用锁生成全局流水号

USE [database]
GO

/****** Object:  StoredProcedure [dbo].[PR_EP_SYS_SEQ_GetNextSequence]    Script Date: 2024/8/15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Description:	利用锁来生成全局流水号

-- userdate:    0为不计日期，1为每天流水号，2为每月流水号，3为每年流水号

-- Return:		日期格式错误返回-1;否则返回可用的流水号
-- =============================================
ALTER PROCEDURE [dbo].[PR_EP_SYS_SEQ_GetNextSequence]
	@type nvarchar(50),	--name_en
	@usedate int
AS
BEGIN
	declare @ret int	
	declare @id nvarchar(36)
	
	set @ret = 0
	
	begin tran	
		if @usedate>0 --流水号每日从1开始
		begin
			if @usedate=2
				select @id=id,@ret=content from EP_SYS_SEQ with(UPDLOCK) 
					where name_en=@type and datediff(month,getdate(),update_time)=0
			else if @usedate=3
				select @id=id,@ret=content from EP_SYS_SEQ with(UPDLOCK) 
					where name_en=@type and datediff(year,getdate(),update_time)=0
			else  
				select @id=id,@ret=content from EP_SYS_SEQ with(UPDLOCK) 
					where name_en=@type and datediff(day,getdate(),update_time)=0
			if @@rowcount=0	
			begin
				set @ret = 1
				if exists (select id from EP_SYS_SEQ where name_en=@type)
					update EP_SYS_SEQ set content=@ret,update_time=getdate() where name_en=@type
				else
					insert into EP_SYS_SEQ(name_en,update_time,content) 
						values(@type,getdate(),1)				 
			end
			else
			begin
				set @ret = isnull(@ret,0)+1 
				update EP_SYS_SEQ set content=@ret,update_time=getdate() where id=@id
			end	
		end
		else
		begin
			select @id=id,@ret=content from EP_SYS_SEQ with(UPDLOCK) where name_en=@type
			
			if @@rowcount=0
			begin
				set @ret = 1
				insert into EP_SYS_SEQ(name_en,update_time,content) 
					values(@type,getdate(),1)
			end		
			else
			begin
				set @ret = isnull(@ret,0)+1
				update EP_SYS_SEQ set content=@ret,update_time=getdate() where id=@id	
			end
				
		end
		
	Commit tran	
			return @ret
END

GO