iVend Database Database : GetNextAccountingId Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@SiteId

In

Reference key of the Site

Integer

4

@Length

In

Length of the id to be generated

Integer

4

@NextnoId

In

Id for which the accounting id has to be generated

VarChar

50

@Result

Out

New Accounting Id generated by the procedure

VarChar

50

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

SysNextNumber

Table

System table

1

/*

begin tran

declare @Result VARCHAR(50)

Exec GetNextAccountingId 1, 20, 'Table.User', @Result OUTPUT

select @Result

rollback

*/

CREATE PROCEDURE [dbo].[GetNextAccountingId]

(

@SiteId   INT,

@Length   INT,

@NextnoId VARCHAR(50),

@Result   VARCHAR(50) OUTPUT

)

AS

BEGIN

  SET NOCOUNT ON

  BEGIN TRANSACTION

DECLARE @ErrorDesc VARCHAR(255)

IF @Length < 5

BEGIN

SET @ErrorDesc = 'Length should be greater then 4.'

    GOTO ERRORHANDLER

END

DECLARE @NextNumber BIGINT

UPDATE SysNextNumber SET NextNumber = NextNumber WHERE NextNumberID = @NextnoId

SELECT @NextNumber = NextNumber

FROM SysNextNumber

WHERE NextNumberID = @NextnoId

IF ISNULL(@NextNumber,0) = 0

BEGIN

  INSERT SysNextNumber ( NextNumberID, NextNumber )

  VALUES( @NextnoId, 1)

  SET @NextNumber = 1

END

UPDATE SysNextNumber

SET  NextNumber = NextNumber + 1

WHERE NextNumberID = @NextnoId

SELECT @Result = RIGHT('0000' + CONVERT(VARCHAR(4), @SiteId) , 4) + REPLICATE ('0', (@Length - LEN(CONVERT(VARCHAR, @NextNumber)) - 4)) + CONVERT(VARCHAR, @NextNumber)

COMMIT

RETURN

ERRORHANDLER:

ROLLBACK

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

RETURN

END