SQL存储过程生产并发不重复的流水号

方案一：

写一个存储过程。
再在另一个需要生成流水号的存储过程里调用。

 SQL code

代码

ALTER PROCEDURE [ dbo ] . [ sp_Sys_CreatId ] (
@tbName VarChar ( 40 ), -- 传入表名
@id bigint OUTPUT) -- 接收生成的id
AS
declare @type NvarChar ( 50 )
declare @maxId bigint
declare @lastDate NvarChar ( 10 )
declare @nowDate NvarChar ( 10 )
BEGIN TRAN
Declare @strYear varchar ( 4 ), @strMonth varchar ( 2 ), @strDay varchar ( 2 )
Set @strYear = DATEPART ( year , GETDATE ())
Set @strMonth = DATEPART ( month , GETDATE ())
Set @strDay = DATEPART ( day , GETDATE ())
if ( Len ( @strMonth ) = 1 )
Set @strMonth = ' 0 ' + @strMonth
if ( Len ( @strDay ) = 1 )
Set @strDay = ' 0 ' + @strDay
Set @nowDate = @strYear + @strMonth + @strDay -- 获取到当前时间
IF ( EXISTS ( SELECT * FROM tbSys_CreateID WHERE sys_TypeNamestr = @tbName ))
BEGIN
select @type = sys_TypeNamestr, @maxId = sys_MaxIdstr, @lastDate = sys_Timedate from tbSys_CreateID where sys_TypeNamestr = @tbName ;

if ( @lastDate = @nowDate ) -- 同一天 加1
set @maxId = @maxId + 1
else -- 不同一天 恢复100001
set @maxid = 100001
set @lastDate = @nowDate
BEGIN
update tbSys_CreateID set sys_MaxIdstr = @maxId ,sys_Timedate = @lastDate where sys_TypeNamestr = @type -- 更新表
End
End
else
BEGIN
insert into tbSys_CreateID(sys_TypeNamestr,sys_MaxIdstr,sys_Timedate) values ( @tbName , 100001 , @nowDate )
set @maxid = 100001
End
set @id = Cast (( @nowDate + cast ( @maxId as varchar ( 6 ))) AS bigint ) * 123 -- 返回值

NeedRollBack:
if @@error > 0
rollback tran
else
commit tran

 

生成流水号表的储存过程里引用

SQL code

代码

ALTER PROCEDURE [ dbo ] . [ tbSupplier_Info_ADD ]
@supp_CompanyNamestr varchar ( 100 ), -- --这里的流水号就不用写了。
AS
declare @id bigint -- -这里开始引用生成流水号的存储过程
exec sp_Sys_CreatId ' tbUser_Info ' , @id out
INSERT INTO [ tbSupplier_Info ] (
[ supp_Idstr ] , [ supp_CompanyNamestr ]
) VALUES (
@id , @supp_CompanyNamestr )

 

 

 

方案二：

建一个种子表，这个种子表就一个字段就是一个自增ID，然后加上日期啊，前缀啊，后缀啊等等就可以实现生成流水号了，这样最简单

SQL code

 

代码

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [ dbo ] . [ GetSerialNumber ]
@length INT ,
@currdate NVARCHAR ( 8 ),
@RegularVal NVARCHAR ( 50 ) output
AS
BEGIN TRY
BEGIN TRAN T1

DECLARE @CurrentValue INT

INSERT INTO 种子表名(CreateTime) VALUES ( Getdate ()) -- 种子表名这个表就两个字段，一个自增字段，一个是时间
SET @CurrentValue = @@Identity
DELETE FROM SequenceItfSeqCode20 WITH (READPAST)

SET @RegularVal = @currdate + RIGHT ( REPLICATE ( 0 , @length ) + CAST (( @CurrentValue ) as NVARCHAR ), @length )

COMMIT TRAN T1
END TRY
BEGIN CATCH
ROLLBACK TRAN T1
END CATCH
SQL code INSERT INTO 种子表名(CreateTime) VALUES ( Getdate ()) -- 种子表名这个表就两个字段，一个自增字段，一个是时间
SET @CurrentValue = @@Identity
DELETE FROM 种子表名 WITH (READPAST)

 

转载于:https://www.cnblogs.com/raychn/archive/2010/07/23/1783684.html