iVend Database Database : GetNextDocumentNumber Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@DocumentNumberSeriesKey

In

BigInt

8

@CommitResult

In

Boolean

1

@Result

Out

Next transaction number generated by the procedure

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

CfgDocumentNumberSeries

Table

Used to store the Document Number series for the applicatiom

1

SysNextNumber

Table

System table

1

/*

begin tran

Declare @abc varchar(50)

Exec GetNextDocumentNumber 10000000000002, @abc output

select @abc

rollback

*/

CREATE PROCEDURE [dbo].[GetNextDocumentNumber]

(

  @DocumentNumberSeriesKey     BIGINT,

@CommitResult BIT,

  @Result NVARCHAR(50) OUT

)

AS

BEGIN

SET NOCOUNT ON

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

DECLARE @NextNumber bigint,

  @ErrorDesc nvarchar(255),

  @TransactionId nvarchar(50),

  @MinNumber INT,

  @Length INT,

  @Prefix NVARCHAR(50),

  @NextNumberID NVARCHAR(50)

SET @NextNumberID = 'DN-' + CONVERT(NVARCHAR, @DocumentNumberSeriesKey)

Select @MinNumber = StartNumber, @Length = NumberLength, @Prefix = Prefix

From CfgDocumentNumberSeries

Where DocumentNumberSeriesKey = @DocumentNumberSeriesKey

Select @NextNumber = NextNumber

From SysNextNumber

Where NextNumberID = @NextNumberID

IF @@RowCount = 0

Begin

INSERT INTO SysNextNumber(NextNumberID, NextNumber)

VALUES(@NextNumberID, @MinNumber)

IF(@@ERROR <>0)

    BEGIN

  SET @ErrorDesc = 'Error in inserting into table Next Number.'

        GOTO ERRORHANDLER

END

SELECT @NextNumber = @MinNumber

End

IF @CommitResult = 'True'

BEGIN

Update SysNextNumber

Set  NextNumber = NextNumber + 1

Where NextNumberID = @NextNumberID

END

Select @TransactionId = Convert(Varchar(50), @NextNumber)

IF (@Length != 0)

SET @TransactionId = IsNull(REPLICATE('0', @Length - LEN(LTRIM(RTRIM((@TransactionId))))-LEN(LTRIM(RTRIM((@Prefix))))), '') + @TransactionId

IF LTRIM(RTRIM(@Prefix)) != ''

SET @TransactionId = RTRIM(LTRIM(@Prefix))+ @TransactionId

SELECT @Result = @TransactionId

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

RETURN

ERRORHANDLER:

RAISERROR('%s',16,-1,@ErrorDesc)

RETURN(1)

END