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 VB.Net 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 VB.Net 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:
RaisError ('Creating Invoice: %s', 16, 1, @err_msg)
RETURN -1
GO
Following code can be used to test the stored procedure using SQL Enterprise Manager: