Search code examples
sqlsql-serversql-server-2008stored-proceduresaccounting

I have missing numbers in the data in sql

Table A
+------------------+  
| Number|   |Name  |    
|  1... |   |test1 |        
|  2... |   |test2 |  
|  3... |   |test3 |  
|  4... |   |test4 |  
|  7... |   |test7 |
|  6... |   |test7 | 
| 10... |   |test10|
|  9... |   |test9 |
+------------------+

I have this table. I use this table for serial numbers for articles. So I miss number 5 and number 8. I need to create store procedure to look through the column Number and finds the missing numbers. I need to give me only the first missing number. I need first to give me result that than firs missing number is 5, then if i insert 5 into the Number column and call again the procedure then the second result to be 8, and so on... Please help. Thank you

Solution

  • You can do this with a simple query:

    select top 1 a.number + 1
from a
where not exists (select 1 from a a2 where a2.number = a.number + 1)
order by a.number;