Unique ID-Generator using SQL Server

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.

SQL - Calling Stored Procedure with Comma Separated List in Input Parameter

Background

Currently I am programming on an ASP.NET web application with a data connection to a MS SQL Server 2008 R2 database. I am fetching data always via stored procedures. Today I came across the problem that I wanted to program a procedure where I have to do a SELECT with a WHERE column IN (...) with an unknown number of arguments in the IN statement.
Hence I was looking for a way to transfer a comma separated list as string parameter into the WHERE IN argument.

First Try

The straight forward approach I tried looked like this:

CREATE PROCEDURE [dbo].[MyProcedure]
@inputList nvarchar(MAX) <- comma separated list of values (string)
AS
BEGIN
      SELECT * FROM myTable
      WHERE
      colName IN (@idList)
END
GO

Unfortunately, this approach does not work. You can inject a list with on list element which will return some reasonable result, but as soon as you call this with list with more than one argument, this fails, as the argument is not interpreted as WHERE IN ('arg1','arg2') but as WHERE IN ('arg1,arg2').

Solution

After some research I stumbled over a Code Project thread which was the key to solve the problem. To have a more ready-to-use description I wrote this blog. With the help of the above thread I wrote the following stored procedure:

CREATE PROCEDURE [dbo].[myWorkingProcedure]
@inputList nvarchar(MAX)
AS
DECLARE @SEPERATOR as VARCHAR(1)
DECLARE @SetPoint INT
DECLARE @VALUE nvarchar(50)
CREATE TABLE #tempTab (id nvarchar(50) not null)
BEGIN
SET NOCOUNT ON;
WHILE PATINDEX('%,%',@idList) > 0 <-- Drive loop while commata exist in the input string
BEGIN
SELECT  @SP = PATINDEX('%,%',@idList) <-- Determine position of next comma
SELECT  @VALUE = LEFT(@idList , @SP - 1) <-- copy everything from the left into buffer
SELECT  @idList = STUFF(@idList, 1, @SP, '') <-- throw away the stuff you copied
INSERT INTO #tempTab (id) VALUES (@VALUE) <-- put value in buffer table
END
INSERT INTO #tempTab (id) VALUES (@idList) <-- insert last value in the input list to buffer
BEGIN
SELECT * FROM myTable
WHERE
myColumn IN (SELECT id FROM #tempTab) <-- Do the select
DROP TABLE #tempTab <-- throw buffer table away
END
END
GO

Conclusion

With the help of the above code it is possible to perform dynamic SELECT WHERE IN statements with an unknown number of arguments. I hope this post helps you to overcome your own problems with transactional SQL


all the best
WolfiG