tsql - Working with sequential numbers in SQL Server 2005 without cursors -

I am currently working on a project that has a process that assigns "control number" to some records is. It can be run at a later date and requires the addition of records without a control number, which assigns an unused control number for these records. These control numbers are predefined by an external unit and are 9 digits long. You will usually receive a category based on how many estimated records your company will generate. For example, one of the companies estimated that they would need 50, so they fixed us 790123401 from the limit of 790123450.

Problem: I am currently using the cursor to assign these numbers. For each person's record, I go and check that the first number in the sequence has already been taken in the table, if it is, then I increase the number and check again. This check continues for every record in the table, in one of the companies there are 17,000 records, which means that for every record, if I have taken all the numbers, then I would probably have 17,000 times in the worst case scenario. I can go

I will actually assign control numbers to a lot of records since the first time all repetition is no objection on the initials. My problem is that if a record changes later and now there should be a control number associated with it, then the process that will run again will mean that I have to go through each available number till I get an unused one .

I have seen many examples on how to use views without the cursor, but most are specific to Oracle I am using SQL Server 2005 for this particular project.

Suggestions?

Are you looking for all unassigned numbers in a range? If so, you can join a number table externally. Example is to create one on the fly below that if I have a maximum size of your range I suggest that there is no permanent address containing at least 17,000 numbers that uses a CTE.

  Announcement @StartRange integer, @EndRange Int SET @StartRange = 790123401 SET @EndRange = 790123450; With YourTable (ControlNumber) (Select 790,123,401 UNION All Select 790,123,402 UNION Select All 790,123,403 UNION Select All 790,123,406), as Nums (N) (Select @StartRange UNION All Select from N 1 Nums Where N & LT; @EndRange) Select N from the number, where is not present (Select your Table ControlNumber = N) option (MAXRECURSION 0)