Dynamic Document ID's without Maintaining Counters!

Introduction

I am sure if any of us who have been dealt with a commercial business application project (Inventory Control, Financial Accounting etc.) must have been challenged with a mechanism to produce "Automatic Document ID" for any of the modules involved. The typical example would be to have "Sales Invoice IDs" to be generated automatically with pattern {yy-nnnnn}, were yy->last two digits of year and nnnnn->00001 incrementing counter.

The most typical solution to this issue would be to have a "counter" maintained in a Table or some Configuration file then update it every time a new record has been created. For many situations this could be a way to go. Then comes the time when you are further challenged with how about doing the same thing without having the headache of maintaining the counter!

I assume the reader of this article is comfortable with understanding of T-SQL and ADO.NET C# environment.

Challenge

I was having a conversation with a friend last week and he asked me if I can help him with something like this. Yes you got it right the issue was exactly as mentioned in the title of this article. The guy was looking to generate document id without the trouble of maintaining the counter. His query took me back to 7 years down the memory lane when one of my clients asked me similar feature in one of the Accounting system I did for them.

It all started like this: my client started a new business stream and they wanted to generate Invoices for this new department, however the trouble they face was to keep generating the Invoices, even when the Financial year is closed, that means they can still generate the Invoice of last fiscal year!

Some thing like this:

For year 2006 - Latest Invoice ID:

06-01230

Now, if they want to generate Invoice belong to last year then without disturbing the current numbers system must find out last year continuation number and start from there:

For year 2005 - Latest Invoice ID:

05-21233

This is all they wanted to achieve it without maintaining any counter, however, they did agree to supply information regarding which year Invoice they want 2005 (past) or 2006 (current).

Solution

The solution I discussed with my friend has helped him as it did helped me in past, I thought why not share this discussion with rest of the community... may be this would help someone in need or just another interesting read, and also I got myself an excuse to write my very first ever article!;)

I am dividing the solution in two parts, first part will be the Stored Procedure and second part would be a simple windows forms C# application to demonstrate the technique.

Stored Procedure:

I am using the "Northwind" database from Sql Server 2000. Please run the following script to create a dummy table called "Invoice", which we will use it to store our dynamically crated document ids.

if exists (select * from dbo.sysobjects

where id = object_id(N'[dbo].[Invoice]')

and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Invoice]

GO

CREATE TABLE [dbo].[Invoice] (

[Invoice_id] [varchar] (10)

COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[Customer_name] [varchar] (50)

COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[Invoice_amount] [money] NOT NULL

) ON [PRIMARY]

GO

Following is the stored procedure code which will take input from user interface and generate new id and store it inside "Invoice" table.

CREATE Procedure insert_invoice_record

/*

** Inserts new record in invoice table with dynamically crated id.

**

** INPUT - PARAMETERS: Year_flag, Customer_Name, Invoice_Amount

** OUTPUT - PARAMETERS: Newly created Invoice ID

** MODIFICATION HISTORY:

** NAME DATE MODIFICATION

** Asif Sayed 27th March, 2006 Original Version

*/

@customer_name varchar(50),

@invoice_amount money,

@year_flag char(1),

@new_invoice_id varchar(10) OUTPUT

AS

SET NOCOUNT ON

SET DATEFORMAT dmy

DECLARE @err_code integer

DECLARE @found_error integer

DECLARE @err_msg varchar(1000)

DECLARE @tmp_invoice_id nvarchar(10)

DECLARE @tmp_date nvarchar(8)

SET @found_error = 0

SET @err_code = 0

-- store current year from date on database server

SET @tmp_date = (SELECT RIGHT(CAST(year(getdate()) AS nvarchar(4)),2))

-- check for year flag (P-Past, C-Current) to be used

IF (@year_flag) = 'P'

BEGIN

-- if year has zero in front minus 1 year from next digit

IF (LEFT(@tmp_date, 1)) = '0'

SET @tmp_date = '0' + CONVERT(NVARCHAR(2),

CONVERT(INTEGER, LEFT(@tmp_date,2)) - 1) + '-'

ELSE

SET @tmp_date=CONVERT(NVARCHAR(2),CONVERT(INTEGER, @tmp_date) - 1) + '-'

END

ELSE

SET @tmp_date = @tmp_date + '-'

-- find max of invoice ids counter from current table to be used to crate

-- new id

SET @tmp_invoice_id = (SELECT MAX(SUBSTRING(invoice_id, 4, 5) + 1)

FROM Invoice WHERE (invoice_id LIKE @tmp_date + '%'))

-- if this is first invoice record then start counter with ....1 else

-- whatever the most recent counter

IF @tmp_invoice_id IS NULL

SET @tmp_invoice_id = '00001'

ELSE

SET @tmp_invoice_id = replicate('0',5-LEN(@tmp_invoice_id)) +

@tmp_invoice_id

-- store new invoice id to output param

SET @new_invoice_id = @tmp_date+@tmp_invoice_id

-- check if any other user has already utilized the newly acquired

-- invoice id

IF EXISTS (SELECT invoice_id

FROM Invoice

WHERE UPPER(invoice_id) = UPPER(@new_invoice_id))

BEGIN

SET @err_msg = '* Invoice ID: ' + @new_invoice_id +

' already exists!, please try saving again!' + CHAR(13)

SET @found_error = 1

END

-- if error found skip insert

IF (@found_error = 1)

GOTO Exception

-- Insert the record in invoice table with new id

INSERT INTO Invoice (invoice_id, customer_name, invoice_amount)

VALUES (@new_invoice_id, @customer_name, @invoice_amount)

-- make a final check to see if any other error happend during process

SET @err_code = @@ERROR

IF (@err_code <> 0)

BEGIN

SET @err_msg = 'Error ' + CONVERT(VARCHAR(20), @err_code)

+ ' occurred while Generating Invoice Record'

GOTO exception

END

RETURN 0

exception: