SQL处理数据并发，解决ID自增

1 创建MaxIdProcess表，由于存储ID的最大值

CREATE TABLE [dbo].[MaxIdProcess](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,    --自增ID
    [TableNM] [nvarchar](200) NOT NULL,    --存储表明
    [Prefix] [nvarchar](50) NULL,    --ID前缀
    [Radix] [char](2) NULL,    --
    [MaxId] [nvarchar](50) NULL,    --存储最大ID
    [CreateDatetime] [datetime] NULL,    --创建时间
    [LastModifyDatetime] [datetime] NULL,
    [LastModifyBy] [nvarchar](50) NULL,
 CONSTRAINT [PK_MaxIdProcess] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

2、创建存储过程 Pro_GetTableNextMaxIdByTableName 获取最大ID

 1 CREATE procedure [dbo].[Pro_GetTableNextMaxIdByTableName]  
 2 @TableName char(50),  --table名称
 3 @Prefix char(3),  --ID前缀
 4 @NextId char(16) out    --ID输出
 5 AS
 6 
 7 begin 
 8     --if not exists (select * from MaxIdProcess where TableName=@TableName)
 9     --begin
10     --    insert into MaxIdProcess values(@TableName,null)
11     --end
12     --    update MaxIdProcess 
13     --        set @NextId= isnull(MaxId, @Prefix + '0000000000001'),
14     --                MaxId = dbo.Fun_GetMaxId(MaxId,@Prefix)
15     --    where TableName=@TableName
16 
17 
18     --检查系统表中是否存在该表，如果不存在则调用Pro_GetRandomStr存储过程获取一个随机ID
19    IF EXISTS (SELECT object_id FROM sys.objects(nolock) WHERE type='U' AND name=@TableName)
20    BEGIN
21         EXEC Pro_GetRandomStr @NextId output
22         RETURN 
23    END 
24     
25     --检查MaxIdProcess表中是否存有需要获取ID的表名，如果不存在则插入数据
26    if not exists (select * from MaxIdProcess where TableNM=rtrim(@TableName))
27     begin
28         insert into MaxIdProcess values(@TableName,@Prefix,'10',0,getdate(),getdate(),'Auto')
29     end
30     
31         declare @temp bigint
32         update MaxIdProcess
33         set @temp=cast(rtrim(MaxId) as bigint),MaxId=MaxId+1
34         where rtrim(TableNM)=rtrim(@TableName)
35         set @NextId=@Prefix+right(cast(1000000000000000+@temp as nvarchar(16)),13)
36 end

3、创建执行存储过程，如插入新增用户

CREATE PROCEDURE [dbo].[Pro_User_Insert]
     @UserId CHAR(16) OUT ,
     @UserNM NVARCHAR(50) ,
     @Description NVARCHAR(255) 
AS

BEGIN TRY
    BEGIN
        DECLARE @Name NVARCHAR(50);
        SELECT  @UserId = UsersTb.UserId ,
                @Name = UsersTb.Description
        FROM    dbo.UsersTb
        WHERE   UsersTb.UserNM = @UserNM;
        IF NOT ISNULL(@UserId, '') = ''
        BEGIN
          SELECT  @UserNM + @Name + '已经存在';
          --SELECT  '用户已经存在';
          RETURN;
        END;
        DECLARE @MaxId CHAR(16);  
        EXEC dbo.GetTableNextMaxIdByTableName 'User', 'Usr', @MaxId OUT;    
        SET @UserId = @MaxId;
        
        INSERT  INTO UsersTb
        (
         [UserId] ,
         [UserNM] ,
         [Description] 
        )
        VALUES
        (
            @UserId ,
            @UserNM,
            @Description
        );
        SELECT  '执行成功';
    END
 END TRY
  BEGIN CATCH
    SELECT  ERROR_MESSAGE(); 
  END CATCH

4、执行新增用户存储过程

DECLARE @UserId int;
EXEC Pro_User_Insert @UserId output,'zhangsan','张三' ;

5、完成

 

关于 Pro_GetRandomStr 存储过程

CREATE Procedure [dbo].[Pro_GetRandomStr]
 @RandomStr varchar(16) output
 as
 BEGIN
    set nocount on
    
    declare @s varchar(61)   
    declare @r varchar(16)   
    declare @pos int
    declare @len int
    set @s = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ012345678'   
    set @len = len(@s);
    set @r = ''   
    while len(@r) < 16   
    begin   
        set @pos = cast(rand()*61 as int);
        --while @pos > @len or @pos <1
        --begin
        --    if(@pos < 1)
        --     set @pos = cast(rand()*61 as int);
        --    else
        --     set @pos = cast(@pos /2 as int);
        --end
        set @r = @r + substring(@s, @pos, 1)
        --select @r
    end
    set @RandomStr =  upper(@r)
END