In SQL 2000 I need to add an additional constraint to a query I use that finds unused numeric PK's in a varchar field.
The current query (below) comes from angelll's solution here:
The constraint I need to add is: return only those values that don't already exist in et_objects.object_id (another table/column). Unfortunately, in that table the values have an 's' prepended in front of them.
For example, if the first unused integer in customers.customer_code is 123 (returned by the current query) but et_objects.object_id contains 's123' and 's124' then I need to return 125 instead.
Any help greatly appreciated.
Here is the current query that returns the smallest unused integer in customers.customer_code:
declare @t table ( v int )
insert into @t (v) select CAST( customer_code AS INT)
WHERE customer_code NOT LIKE '%[^0-9]%'
select min(t.v) + 1
from @t t
left join @t n
on n.v = t.v + 1
where n.v is null