Solved

Generate unique number

Posted on 2000-02-14
9
302 Views
Last Modified: 2012-05-04
I have a Clerk table that contains a LogonCode field of type int - it is a unique key.  
I am writing a program that uses this table and when a new Clerk is created I would like to get the next valid Unique Logon code from the table.  Is there any SQL code that can do this???

Thanks
0
Comment
Question by:I_jolly
  • 5
  • 3
9 Comments
 
LVL 9

Expert Comment

by:david_levine
Comment Utility
You have some options:

1) You could setup the datatype of that column to be Identity and the system would handle it for you.

2) You could have another table with 2 columns that keeps track of the key (say Clerk) and the next number to use. You update that table and use the next number.

3) you can select max(LogonCode) from the table and use that+1.

David
0
 

Author Comment

by:I_jolly
Comment Utility
I want to use a form of loop within SQL whereby I check if the number exists in the table and if it doesn't then select that number.
0
 

Author Comment

by:I_jolly
Comment Utility
Sorry,  Forgot to thank David for his last answer.
0
 
LVL 9

Expert Comment

by:david_levine
Comment Utility
No, you don't want to use a loop. Not in SQL anyway.

Can you let me know my answers were not acceptable. They would give you the rseults you requested and even the comment you made would be satisfied by one or all of those answers.

David
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:I_jolly
Comment Utility
1, Identity - This assumes that I will be inserting the record into the database - After a new Clerk is created by the user it is not Necessarily persisted to the database.

2, I don't want to add a new table to the existing database - Although this would provide a solution I am sure that there is another method of doing this without adding additional tables \ DRI to the database.

3, max(LogonCode) from the table and use that+1 - The LogonCode would only increment.  I am expecting that users will constantly be added and removed - Using this method could lead to problems - (There is a business Rule within the Program that LogonCodes must be in the range 0 - 999).

Why should I not use a loop.  I had in mind something like this

BEGIN
      If Not EXISTS (Select PKClerkID from CLERK Where LogonCode = @COUNTER)
            BREAK
      ELSE
            SELECT @COUNTER = @COUNTER +1
END
Print @Counter

Thanks
0
 
LVL 9

Expert Comment

by:david_levine
Comment Utility
Well in a real world environment, with hundreds of users and tens of thousands of transactions a day, your idea is no good for the following reasons...

1) You might need to process through a large amount of records every time you need to find a number... bad.

2) Two people, running the same query at almost the same time, could get the same number back... bad.

3) Where's the loop in your code?

You need to design your applications intelligently. This isn't a personal attack against you. I've just inherited code in the past that was written like you suggest and it's a nightmare to maintain and enhance. Write it correctly the first time. You aren't taking advantage of the power and capabilities in SQL.

David
0
 

Author Comment

by:I_jolly
Comment Utility
SELECT @COUNTER = 1
WHILE (@COUNTER < 999)

I did miss the top out.  There will be no concurrency within the program I am coding and the Business Rule limits the size to 999.  I do agree with you that it may cause problems later on if the program is enhanced - So will look into your answers again
0
 
LVL 2

Accepted Solution

by:
ginde earned 30 total points
Comment Utility
You do not need loop for what you are tring to do. You can also do it as

If exists ( select id from yourtable where id = 1 )
select @newid = min(id) + 1
from yourtable a
where not exists ( select id from yourtable b where b.id = a.id+1)
ELSE
 @newid = 1

This gives you the minimum id available.

-Ginde
0
 

Author Comment

by:I_jolly
Comment Utility
It's beautiful.....

I do agree that all of Davids previous answers are also correct but this was exactly what I was looking for.

Thanks everyone
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now