SQL Server generates document numbers automatically by time

SET @_tmpdatetime = GETDATE ()

EXEC dbo. Dtw_common_generateproofcode @ProofType = ' so ', @[email protected], @ProofDate = @_tmpdatetime,
@RtnCode = @_tmpproofcode OUTPUT--the final code generated

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 (+) = ",--table name

@FiledName nvarchar (128) = ",

@ProofTypenvarchar (+),--documents abbreviated to two-digit

@ProofDate Datetime= ',

@RtnCodenvarchar = null output,--generated by the final code

@WhsCode varchar (10) = "

)

As

--Using new ways

--if (@WhsCode is null Or @WhsCode = ')

--select @WhsCode =code from W_inf_warehouse where id=1

if (@WhsCode = ") set @WhsCode =db_name ()--only the Standard Edition uses the warehouse code
Else
Set @WhsCode =left (@WhsCode, 4)

DECLARE @Name varchar (50)

Set @[email Protected][email protected]

exec [Up_getmessageid] @[email protected], @EDICode = ', @[email protected] 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
/*
Add a list of generated serial numbers: W_sn_edi 1) edicode nvarchar (2) Serialno int

declare @s nvarchar (100)

exec dbo.up_getmessageid ' dtwehs ', ' IR ', @s output

Print @s
*/

ALTER Procedure [dbo]. [Up_getmessageid]
(
@Name nvarchar = ' DTW01 ',
@EDICode nvarchar (8),
@RMessageId nvarchar () output,
@IsLongYear int=0,
@ItemCount int=5,--the length of the serial code
@IsClear Bit=1,--whether to clear zero
@SplitChar varchar (1) = ",
@CodeQty Int=0-The number of barcodes required, note that not the number of copies, the number of copies is the same per bar code, and quantity refers to a continuous number
)
As
SET TRANSACTION Isolation Level SERIALIZABLE
Begin TRANSACTION--Start a transaction
DECLARE @IsReturnTable bit
Set @IsReturnTable =0
if (@CodeQty >0)
Begin
--In order to be compatible with the previous system, it is not necessary to return a table without specifying @codeqty, only use @rmessageid to return a number
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)--Short year + month
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][email protected] and [email protected])
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][email protected] and [email protected] and [Date][email protected])
Begin
Update W_sn_edi set serialno [email protected],[date][email protected]
where [Name][email protected] and [email protected]
if (@ @Error <>0) Goto ERR
End
Else
Begin
Select @SerialNo = Serialno from W_sn_edi
where [Name][email protected] and [email protected] and [Date][email protected]

Update W_sn_edi Set Serialno = Serialno + @CodeQty
where [Name][email protected] and [email protected] and [Date][email protected]
if (@ @Error <>0) Goto ERR
End
End
Else
begin--not clear Zero
Select @SerialNo = Serialno from W_sn_edi
where [Name][email protected] and [email protected]
If @SerialNo =99999 set @SerialNo =0
Update W_sn_edi Set Serialno = @SerialNo + @CodeQty
where [Name][email protected] and [email protected]
if (@ @Error <>0) Goto ERR
End
End
DECLARE @end int
Set @[email Protected][email protected]
SET @RMessageId = ' '
Declare @Tab table (Id int identity (), No varchar (50))
Set @[email protected]+1
while (@SerialNo <[email protected])
Begin
Set @[email protected][email protected][email protected]+ @[email protected]+ (REPLICATE (' 0 ', @ItemCount-len (@SerialNo )) +cast ((@SerialNo) as nvarchar (10)))
Insert into @Tab values (@RMessageId)
Set @[email protected]+1
End
if (@IsReturnTable =1)
Select Id,no from @Tab

Commit TRANSACTION--Commit Transaction
Return

Err:
Begin

RAISERROR (' Generate MessageID failed:%s%s ', 16,1, @Name, @EDICode)
ROLLBACK TRANSACTION

End

SQL Server generates document numbers automatically by time