Auto-Number without using Identity Column in MS-SQL Server

[gm_dwivedi]

Hi,

I have a table without any Identity field. The table contains a coulmn as 'ReqID'. The format of the data in the column 'ReqID' should be 'MMDDYYYYNNNNN', where

MMDDYYYY = Current Date
NNNNN=Unique number in sequesnce, within range 10000-99999 (The number should be in sequence[NOT RANDOM] for each new row inserted into table)

i.e. the value will be created by MMDDYYYY+NNNNN.

I am able to have MMDDYYYY from getDate() cammand. Now, Problem is to generate the auto-number 'NNNNN'. I can not introduce any NEW Identity column (or a new table). Also the records may be inserted using more than one database connections.

Please help me, how to do it?

Ya, one thing that.... I can change the existing column 'ReqID' as identity column, but then how to incoperate date part??

Thanks in advance.
Gyanendra Dwivedi
gm_dwivedi@sify.com

[Rune Bivrin]

CREATE TABLE TheTable (ReqID NVARCHAR(13) PRIMARY KEY CLUSTERED, Data NVARCHAR(100))


DECLARE @date NVARCHAR(8)
SET @date = '05282006'
INSERT INTO TheTable (ReqID, Data) 
SELECT @date + CONVERT(NVARCHAR(5), CONVERT(int, SUBSTRING((SELECT ISNULL(MAX(ReqID), @date + '09999') FROM TheTable WHERE ReqID LIKE @date + '%'), 9, 5) + 1)), 'should be 0528200610000'

INSERT INTO TheTable (ReqID, Data) 
SELECT @date + CONVERT(NVARCHAR(5), CONVERT(int, SUBSTRING((SELECT ISNULL(MAX(ReqID), @date + '09999') FROM TheTable WHERE ReqID LIKE @date + '%'), 9, 5) + 1)), 'should be 0528200610001'

SET @date = '05292006'
INSERT INTO TheTable (ReqID, Data) 
SELECT @date + CONVERT(NVARCHAR(5), CONVERT(int, SUBSTRING((SELECT ISNULL(MAX(ReqID), @date + '09999') FROM TheTable WHERE ReqID LIKE @date + '%'), 9, 5) + 1)), 'should be 0529200610000'

INSERT INTO TheTable (ReqID, Data) 
SELECT @date + CONVERT(NVARCHAR(5), CONVERT(int, SUBSTRING((SELECT ISNULL(MAX(ReqID), @date + '09999') FROM TheTable WHERE ReqID LIKE @date + '%'), 9, 5) + 1)), 'should be 0529200610001'

select * from TheTable

DROP TABLE TheTable

It would, of course, be a good idea to wrap that icky code in a user-defined function...
Or you could have a trigger generate the key for you.

Rune

[gm_dwivedi]

@Rune
Thanks for reply.
I have got another similar solution for the problem.
Please visit
http://www.codeguru.com/forum/showth...75#post1411375
for details.

-Gyanendra Dwivedi