Background
In a project of mine I had the challenge to generate IDs which are profixes of file names of QM documents we want to store on a file server. These IDs have to be unique and have the follwoing structure:
<area of application, char(4)>-<document type char(3)>-<responsible unit, char(4)>-
<running number, char(5)>-...... (user title)
The parts "area of application", "document type" and "responsible unit" are prescribed by guidelines, the challenge was to build some central tool where users can "pick" new ID which has to be unique.
DB-Tables
I build four DB-tables:
ApplicationAreas with columns
- ApplicationAreaID (char4), PK)
- Description (nvarchar(100))
DocumentTypes with columns:
- DocumentTypeID (char(3), PK)
- Description (nvarchar(100))
ResponsibleAreas with columns:
- ResponsibleArea (char(4), PK)
- Description (nvarchar(100))
DocumentIDs with columns:
- ApplicationArea (char(4), PK, FK to ApplicationAreas.ApplicationArea)
- DocumentType (char(3), PK, FK to DocumentTypes.DocumentType
- ResponsibleArea (char(4), PK, FK to ResponsibleAreas.ResponsibleArea)
- RunningNumber (int, PK)
Stored Procedure
My first attempt was to make column DocumentIDs.RunningNumber an identity column and to calculate the number automatically via the table. Unfortunately, this approach failed, as the identity functionality does not take into account the multi-column primary key and simply counts up the number with every new entry.
Finally I choose to take an approach via a stored procedure which gives a string output with the automatically determined Document-ID. Here the code:
USE [theDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pickDocumentID]
@ApplicationArea char(4),
@DocumentType char(3),
@ResponsibleUnit char(4)
AS
BEGIN
DECLARE @counter int
DECLARE @counterchar char(5)
DECLARE @theOutput nvarchar(255)
DECLARE @theLength int
SET NOCOUNT ON;
-- Retrieve highest existing entry for given values
SET @counter = (SELECT MAX(RunningNumber) FROM DocumentIDs
WHERE
ApplicationArea = @ApplicationArea AND
DocumentType = @DocumentType AND
ResponsibleUnit = @ResponsibleUnit);
-- If there is no entry for the given combination of first three
-- columns initialize @counter
IF @counter IS NULL
SET @counter = 0;
SET @counter = @counter + 1;
-- write newly determined Id to DB
INSERT INTO DocumentIDs
(ApplicationArea, DocumentType, ResponsibleUnit, RunningNumber)
VALUES
(@ApplicationArea, @DocumentType, @ResponsibleUnit, @counter)
-- Output for Document ID ready to use
SET @counterchar = CONVERT(char(5),@counter);
SET @theLength = LEN(@counterchar);
SET @counterchar = CASE
WHEN @theLength = 1 THEN '0000' + @counterchar
WHEN @theLength = 2 THEN '000' + @counterchar
WHEN @theLength = 3 THEN '00' + @counterchar
WHEN @theLength = 4 THEN '0' + @counterchar
WHEN @theLength = 5 THEN @counterchar
END
SET @theOutput = @ApplicationArea + '_' +
@DocumentType + '_' +
@ResponsibleUnit + '_' +
@counterchar ;
SELECT @theOutput;
END
So, this was it dear reader. I hope you could get something out of my post and you enjoyed.
