1

I'm currently working on a project that needs to have a process that assigns "control numbers" to some records. This also needs to be able to be run at a later date and include records without a control number that changed, and assign an unused control number to these records. These control numbers are preassigned by an outside entity and are 9 digits long. You would usually get a range depending on how many estimated records your company will generate. For example one of the companies estimated they would need 50, so they assigned us the range 790123401 to 790123450.

The problem: right now I'm using cursors to assign these numbers. For each individual record, I go and check if the first number in the sequence is already taken in the table, if it is, I increment the number, and recheck. This check goes on and on for each record in the table. One of the companies has 17,000 records, which means that for each of the records, I could be potentially iterating at worst 17,000 times if all numbers have been taken.

I really don't mind all the repetition on the initial run since the first run will assign control numbers to a lot of records. My problem is that if later a record gets changed and now should have a control number associated with it, then re-running the process would mean it would go through each available number until I get an unused one.

I've seen numerous examples on how to use sequences without cursors, but most are specific to Oracle. I'm using SQL Server 2005 for this particular project.

Suggestions?

enriquein
  • 1,048
  • 1
  • 12
  • 28

1 Answers1

3

You are looking for all unassigned numbers in a range? If so you can outer join onto a numbers table. The example below uses a CTE to create one on the fly I would suggest a permanent one containing at least 17,000 numbers if that is the max size of your range.

DECLARE @StartRange int, @EndRange int
SET @StartRange = 790123401
SET @EndRange = 790123450;

WITH YourTable(ControlNumber) AS
(
SELECT 790123401 UNION ALL
SELECT 790123402 UNION ALL
SELECT 790123403 UNION ALL
SELECT 790123406
),
Nums(N) AS
(
SELECT @StartRange
UNION ALL
SELECT N+1
FROM Nums
WHERE N < @EndRange
)
SELECT N 
FROM Nums
WHERE NOT EXISTS(SELECT * 
                 FROM YourTable 
                 WHERE ControlNumber = N )
OPTION (MAXRECURSION 0)
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Wow, pretty amazing. I need to look into CTE's and their syntax as all of this is alien to me. Going to try this out with real data. If it all works, you can count on being the answer. – enriquein Mar 17 '11 at 13:56
  • @enriquein - re: The (disappeared!) comment on performance. But of course the permanent numbers table would just need to go from 0-20,000 say. Then you could do `SELECT N+@StartRange` it wouldn't need to contain every possible number. A good answer discussing auxiliary numbers table is here http://stackoverflow.com/questions/10819/sql-auxiliary-table-of-numbers/2663232#2663232 – Martin Smith Mar 17 '11 at 17:10
  • No I deleted that comment because it wasn't true. The part that created the numbers executed in less than a second. The nasty part that took almost a minute was actually joining that table with my data (since using the subselect took over 8 minutes and didn't yield all results). I think I might be able to polish it a bit and make it usable. It's already performing at half the time of what my cursor implementation is running. – enriquein Mar 17 '11 at 18:03
  • Thank you very much Martin. Every bit of info you provided for this question led me to very interesting bits I didn't know about. If I could mark you as answer twice, I would. – enriquein Mar 17 '11 at 19:58