Ad:

MS SQL Discussion Thread
Unsolved
Views: 978

Sep 15th, 2009

Interesting Unique Id generation Problem

Expand Post »

Hi All SQL Gurus,

I am working on a project for a college. I have to generate a unique student Id for each student. I know I can use IDENTITY column to achieve this but I have rather complex situation here. My studentID field is string because they want it in a format of
StudentID = Last Two Digit of the year + term they study + 3digit number.

They have 8 different terms so for example if student enroll in 2009 for term 2 his ID will be 0902XXX. where XXX is the unique number part. They want this unique number part to start at 1 for each year and term. So 1st student in year 2009 of term 1 will have Id 0901001 and 1st student of the term 2 will have Id 0902001.

How can I achieve this ?

One of the solution that I though of is to create a table with fields Year, term and currentNumber. My issue with this solution is how can I keep currentNumber field unique for each request. I am making a web application potentially used by many members at same time.

Please suggest any alternative solution.

Similar Threads

Vista Local Access Only Problem in Windows Vista and Windows 7
email generation problem in ASP.NET
shutdown message problem in Windows NT / 2000 / XP
Is Halting Problem Valid for P(the class of all Pascal programs )? in Pascal and Delphi
please help me as soon as possible in C++

virang_21

Reputation Points: 10

Solved Threads: 2

Light Poster

Offline

29 posts
since Jan 2009

Permalink

Sep 16th, 2009

Re: Interesting Unique Id generation Problem

As per your requirement, the three digit number will be the 'maximum value of 3 digit number (for the year and term ) + 1'

Therefore you calculate the number(ie max + 1) only when you save the student details to the database. Therefore the currentNumber will be unique for each student. Also make the combination of Year, term and currentNumber as composite primary key. Hence concurrent requests cannot have the same maximum currentNumber.

Hope this will help you.

Ramesh S

Reputation Points: 165

Solved Threads: 113

Posting Pro

Offline

580 posts
since Jun 2009

Permalink

Sep 21st, 2009

Re: Interesting Unique Id generation Problem

Well I resolve this issue by using a table with just one field ID and made it IDENTITY and set its max value to 999 and then used the following procedure to get the next number for 3 digit number part. My year and term part is in another table and I use those values to create my studentId and then update all the tables in the database with new studentId in a single transaction.

MS SQL Syntax (Toggle Plain Text)
CREATE PROCEDURE [dbo].[get_next_id](@ID INT OUTPUT) AS
SET NOCOUNT ON
DECLARE @Current int;
SELECT @Current=[ID] FROM [IdGenerator];
IF @Current<1000
BEGIN
	UPDATE IdGenerator SET @ID = ID, ID = ID + 1
END
ELSE
BEGIN
	UPDATE IdGenerator SET @ID =1,ID = 2 
END
CREATE PROCEDURE [dbo].[get_next_id](@ID INT OUTPUT) AS
SET NOCOUNT ON
DECLARE @Current int;
SELECT @Current=[ID] FROM [IdGenerator];
IF @Current<1000
BEGIN
	update IdGenerator SET @ID = ID, ID = ID + 1
END
ELSE
BEGIN
	update IdGenerator SET @ID =1,ID = 2 
END

virang_21

Reputation Points: 10

Solved Threads: 2

Light Poster

Offline

29 posts
since Jan 2009

Permalink

Sep 21st, 2009

Re: Interesting Unique Id generation Problem

You should use a trigger on the insert to generate the id instead of a sproc to get the next value. Even then your sproc should probably be a UDF. In your case if the transaction is rolled back the ID will still be incremented even if the record is not inserted I believe.

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Is it logical to change SQL permissions from 3rd party app?

Next Thread in MS SQL Forum Timeline: query order problem

DaniWeb Message

Cancel Changes

User Name
Password