110,568
社区成员
发帖
与我相关
我的任务
分享
INSERT INTO 种子表名(CreateTime) VALUES(Getdate())-- 种子表名这个表就两个字段,一个自增字段,一个是时间
SET @CurrentValue=@@Identity
DELETE FROM 种子表名 WITH (READPAST)
-- =============================================
-- 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
-- =============================================
-- 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
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
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)