Solved

Generate unique number

Posted on 2000-02-14
9
304 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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 30 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql server tables from access 18 19
Return 0 on SQL count 24 30
Connecting to multiple databases to create a Dashboard 5 25
divide by zero error 23 15
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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 SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

773 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