SqlServer按时间自动生成生成单据编号
SET @_tmpDateTime = GETDATE()
EXEC dbo.Dtw_Common_GenerateProofCode @ProofType = 'SO',@WhsCode=@WhsCode, @ProofDate = @_tmpDateTime,
@RtnCode = @_tmpProofCode OUTPUT --生成的最终的CODE
USE [SZVB]
GO
/****** Object: StoredProcedure [dbo].[Dtw_Common_GenerateProofCode] Script Date: 2015/4/8 11:52:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Dtw_Common_GenerateProofCode]
(
@TableName nvarchar( 128 )='', --表名
@FiledName nvarchar(128)='',
@ProofType nvarchar( 32 ) , --单据简称 两位
@ProofDate datetime='',
@RtnCode nvarchar( 64 ) = null output, --生成的最终的CODE
@WhsCode varchar(10)=''
)
AS
--使用新方式
--if(@WhsCode is null Or @WhsCode='')
--select @WhsCode=Code from W_inf_Warehouse where id=1
if(@WhsCode='')set @WhsCode =DB_NAME()--只有标准版使用仓库编码
else
set @WhsCode=LEFT(@WhsCode,4)
declare @Name varchar(50)
set @Name=@ProofType+@WhsCode
exec [up_GetMessageId] @Name=@Name,@EDICode='',@RMessageId=@RtnCode output,@IsLongYear=1,@ItemCount=4
return
USE [SZVB]
GO
/****** Object: StoredProcedure [dbo].[up_GetMessageId] Script Date: 2015/4/8 13:49:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
增加一张生成序列号表:w_SN_EDI 1)EDICode nvarchar(50) 2)SerialNo int
declare @s nvarchar(100)
exec dbo.up_GetMessageId 'DTWEHS','IR',@s output
print @s
*/
ALTER Procedure [dbo].[up_GetMessageId]
(
@Name nvarchar(100) ='DTW01',
@EDICode nvarchar(8),
@RMessageId nvarchar(100) output,
@IsLongYear int=0,
@ItemCount int=5 , --流水码长度
@IsClear bit=1, --是否清零
@SplitChar varchar(1)='',
@CodeQty int=0 --需要条码的数量,注意不是份数,份数是每个条码相同,而数量是指连续的号码
)
AS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION --开始一个事务
declare @IsReturnTable bit
set @IsReturnTable=0
if(@CodeQty>0)
begin
--为了兼容以前系统，对于没有指定@CodeQty的不需要返回表，仅适使用@RMessageId返回一个号码
set @IsReturnTable=1
end
else
begin
set @CodeQty =1
end
declare @SerialNo int,@Date nvarchar(8)
if(@IsLongYear=1)
set @Date=convert(nvarchar(6),getdate(),12)
else if(@IsLongYear=2) --短年+月份
set @Date=convert(nvarchar(4),getdate(),12)
else
set @Date=convert(nvarchar(8),getdate(),112)
set @SerialNo = 0
if not exists(select 1 from w_SN_EDI where [Name]=@Name and EdiType=@EDICode)
begin
Insert Into w_SN_EDI([Name],EdiType,[Date],SerialNo)
select @Name,@EDICode,@Date,@CodeQty
if(@@Error<>0) goto err
end
else
begin
if @IsClear=1
begin
if not exists(select 1 from w_SN_EDI where [Name]=@Name and EdiType=@EDICode and [Date]=@Date)
begin
update w_SN_EDI set serialno =@CodeQty,[Date]=@Date
where [Name]=@Name and EdiType=@EDICode
if(@@Error<>0) goto err
end
else
begin
select @SerialNo = serialno from w_SN_EDI
where [Name]=@Name and EdiType=@EDICode and [Date]=@Date
update w_SN_EDI set serialno = serialno + @CodeQty
where [Name]=@Name and EdiType=@EDICode and [Date]=@Date
if(@@Error<>0) goto err
end
end
else
begin--不清零
select @SerialNo = serialno from w_SN_EDI
where [Name]=@Name and EdiType=@EDICode
if @SerialNo=99999 set @SerialNo=0
update w_SN_EDI set serialno = @SerialNo + @CodeQty
where [Name]=@Name and EdiType=@EDICode
if(@@Error<>0) goto err
end
end
declare @end int
set @end=@SerialNo+@CodeQty
SET @RMessageId = ''
declare @Tab table(Id int identity(1,1),No varchar(50))
set @SerialNo=@SerialNo+1
while(@SerialNo<=@end)
begin
set @RMessageId=@Name+@SplitChar+@EDICode+ @SplitChar+@Date+(REPLICATE('0',@ItemCount-len(@SerialNo))+cast((@SerialNo) as nvarchar(10)))
Insert Into @Tab values(@RMessageId)
set @SerialNo=@SerialNo+1
end
if(@IsReturnTable=1)
Select Id,No From @Tab
COMMIT TRANSACTION --提交事务
Return
err:
begin
raiserror('生成MessageId失败:%s%s',16,1,@Name,@EDICode)
ROLLBACK TRANSACTION
end
