Solved

Generate unique number

Posted on 2000-02-14
9
305 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
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…

830 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