Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Generate unique number

Posted on 2000-02-14
9
Medium Priority
?
309 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
ID: 2518975
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
ID: 2522134
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
ID: 2522136
Sorry,  Forgot to thank David for his last answer.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 9

Expert Comment

by:david_levine
ID: 2522167
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
 

Author Comment

by:I_jolly
ID: 2522271
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
ID: 2522293
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
ID: 2522468
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 120 total points
ID: 2523413
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
ID: 2523814
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

879 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