Generate Order Number in Bulk

I was asked by developers if we could generate an alphanumeric order number in the database, rather than in the code.  The number is in the style of A00001.  When it hits A99999, then it moves on to B00001.

The main challenge is that we are working with sets of records in this situation.  Most order number generation deals with one order at a time.  And, to foreshadow the solution, I eventually did have to look at them one at a time.

I’ll just dive into it.  It was much more complicated than this, but I’m reducing it to the essentials for this blog post.

Function to get Max Order Number so far…

CREATE FUNCTION [dbo].[getMaxNumberOrder]
RETURNS CHAR(6)
AS
BEGIN

DECLARE @PreviousFullOrderNumber VARCHAR(150);
DECLARE @PreviousOrderNumber CHAR(6);

SELECT @PreviousFullOrderNumber = MAX(RTRIM(OrderNumber))
FROM dbo.[Order]

SET @PreviousOrderNumber = RIGHT(@PreviousFullOrderNumber, 6);

IF @PreviousOrderNumber IS NULL
SET @PreviousOrderNumber = 'A00000';

RETURN @PreviousOrderNumber;

END

GO

Function to get Next Number so far…

CREATE FUNCTION [dbo].[getNextNumber]
(
@PreviousNumber CHAR(6)
)
RETURNS CHAR(6)
AS
BEGIN

DECLARE @PreviousInt INT;
DECLARE @PreviousChar CHAR(1);

SET @PreviousInt = CAST(RIGHT(@PreviousNumber,5) AS INT);
SET @PreviousChar = LEFT(@PreviousNumber,1);

DECLARE @NextInt INT;
DECLARE @NextChar CHAR(1);
DECLARE @NextNumber CHAR(6)

IF @PreviousInt = 99999 /* If it hit the max, then set OrderInt to 0, and increase Letter Prefix */
BEGIN

SET @NextInt = 0;
IF @PreviousChar = 'Z'
BEGIN
SET @PreviousChar = 'A';
END
ELSE
BEGIN
SET @NextChar = CHAR( ASCII(@PreviousChar) + 1);
END
END
ELSE
BEGIN

SET @NextInt = @PreviousInt + 1;
SET @NextChar = @PreviousChar;

END

SET @NextNumber = @NextChar + RIGHT('0000' + CAST( @NextInt AS VARCHAR(5) ), 5);

RETURN @NextNumber

END

GO

Code to assign next order number to order.

Put data into a temp table, and assign an int integer to it.

IF OBJECT_ID('tempdb..#DataTable') IS NOT NULL
DROP TABLE #DataTable

CREATE TABLE #DataTable
(
[IdentityInt] INT IDENTITY(1,1) NOT NULL,
OrderAmount MONEY,
OrderQuantity int
);

 

/**********************************
*
* Begin generate Order Number
*
***********************************/

DECLARE @PreviousOrderNumber CHAR(6);
SET @PreviousOrderNumber = dbo.getMaxNumberOrder()

 

DECLARE @NextOrderNumber CHAR(6);

DECLARE @WhileLoopInt_Order INT;
SET @WhileLoopInt_Order = 1;

DECLARE @MaxWhileLoopInt_Order INT;
SELECT @MaxWhileLoopInt_Order = MAX([IdentityInt]) FROM #DataTable

WHILE (@WhileLoopInt_Order <= @MaxWhileLoopInt_Order )

BEGIN

SET @NextOrderNumber = dbo.getNextNumber(@OrderNumberPrefix, @PreviousOrderNumber)

UPDATE #DataTable
SET OrderNumber = @OrderNumberPrefix + '-' + @NextOrderNumber
WHERE [IdentityInt] = @WhileLoopInt_Order;

SET @PreviousOrderNumber = @NextOrderNumber;
SET @WhileLoopInt_Order = @WhileLoopInt_Order + 1

END

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s