I need an SQL set generation function...I think!

[JamesHardiman]

I have a DB that allocates guest numbers. Don't want to use AutoNumber, because I need sequential numbers.

Also, I want to re-use deleted numbers.

At present I do this:
intRangeLower = DLookup("RangeStart", "tblAvailableNumbersRanges", "TableName='" & t & "'&quot
intRangeUpper = DLookup("RangeEnd", "tblAvailableNumbersRanges", "TableName='" & t & "'&quot

s = "SELECT MIN(AvailableNumber) as n from tblAvailableNumbers " _
& "WHERE AvailableNumber NOT in (Select " & f & " from " & t & &quot " _
& &quot;AND AvailableNumber >= &quot; & intRangeLower & &quot; AND AvailableNumber <= &quot; & intRangeUpper

which involves me having to have a table of numbers, and a table of ranges. All of which is a bit naff.

Basically, if I have a table that looks like this:

1 Fred Smith
2 Joan Smith
4 Anne Jones

I want a routine (SP, VB; I don't mind), that will return 3, this time, and after I've written away record 3, it will return 5.

Anyone any ideas?

James Hardiman
JamesH@sunsail.com

 

[bigfoot]

If the numbers are all in sequence, you could do some math on the fly. This may be a but rash, but you could do:

Get the min, then look at the next record, and see if it is equal to min+1. If not then your next number is min + 1.

Sorry. It's early here. Just something to think about.

 

[JamesHardiman]

Mmm.

I think that's even less elegant than what I'm doing!

I could have several thousand records, and I've no way of knowing where the &quot;gaps&quot; in the sequence might be!

What I'm doing works fine--has been for three years--but every time I look at the code, I get this feeling that there has to be a more elegant way to do it!

James

 

[JamesLean]

OK, let's say you have a table called MyTable and the PK field called MyPK. You can get the lowest available number like this:

SELECT ISNULL(MIN(t1.MyPK) + 1, 1)
FROM MyTable t1 LEFT JOIN MyTable t2 ON t1.MyPK + 1 = t2.MyPK
WHERE t2.MyPK IS NULL

I use ISNULL so it returns 1 when the table is empty.

NB: have just tried this again and it works for every scenerio except when the lowest available number is lower than the lowest existing number.

Will try and fix this but hopefully this should give you something to work on...

--James

 

[snag56]

For example:
I have the following State table which contains 52 rows,
starting with identity of 12.
I know in your case, you are not using the identity property,
but it was easier for me to illustrate my solution,
and it shouldn't effect my results.

CREATE TABLE [dbo].[State] (
[ID] [int] IDENTITY (12, 1) NOT NULL ,
[Abbreviation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Last_Update_Date] [datetime] NOT NULL ,
[Last_Update_ID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[State] WITH NOCHECK ADD
CONSTRAINT [DF_State_Last_Update_Date] DEFAULT (getdate()) FOR [Last_Update_Date],
CONSTRAINT [PK_State] PRIMARY KEY NONCLUSTERED
(
[ID]
) ON [PRIMARY]
GO

If I want find the first unused/available id/key,
I would use the following code:

SELECT IDENTITY(int, 1,1) AS ID_Num , convert(int,[ID] ) as state_id
into #gapper
from state

select top 1 ID_Num from #gapper
where ID_Num <> state_id

if @@ROWCOUNT = 0 select max(ID_Num) + 1 as ID_Num from #gapper

drop table #gapper

Notice that when creating the temp table #gapper,
I used an identity seed value of 1.
In this case, the 2nd select would return 1, then 2, then 3, and so on,
until I get to 11.
Then it would return the next available gap (if any).
If no gap is found,
then the 3rd select will return the next assignable id,
after the max(id).

If you don't want to use Id's 1 through 11,
then you just adjust the identity seed value
in the 1st select accordingly.
For example, if I set it to 12,
then my first query returns the next assignable id,
using the 3rd select.

A word of caution, in that this solution
probably isn't very scalable.
If you are dealing with a table with many thousands of
rows, and a high application concurrancy rate,
you may be better off using the standard identity property,
and adjust your application to deal with it.
I tested this with table of 8000+ rows,
and it performed well.

 

[JamesHardiman]

I haven't tried Snag56's code yet, but I've got this SP that deals with the case that James Lean said he hadn't. Not stunningly elegant, but it works:

Alter Procedure spNextNoReuse
@Num1 int=0,
@Num2 int=0
As
SELECT @num1=ISNULL(MIN(t1.GuestId) - 1, 1) FROM tblG t1 LEFT OUTER JOIN
tblG t2 ON t1.GuestId - 1 = t2.GuestId
WHERE (t2.GuestId IS NULL)

SELECT @num2=ISNULL(MIN(t1.GuestId) + 1, 1) FROM tblG t1 LEFT OUTER JOIN
tblG t2 ON t1.GuestId + 1 = t2.GuestId
WHERE (t2.GuestId IS NULL)

SELECT
case when (@num1 < @num2) and (@num1>=0)
then @num1
else @num2
end as NewId

It gives the largest available number lower than the lowest existing number, but that doesn't really matter. I don't care if gaps in the sequence are filled from the top or the bottom of the gap. I haven't generalised it to handle any given field for any given table, but I expect I can hack that.

Next question... I'm a long way short of an expert on Stored Procedures. Is there any neat way that I can get data from an SP back into VB, as though I'd called a VB function? At present I set an ADO recordset with the SP as the source, and then read the record, but that seems long-winded.

I've got a couple of SPs that use an OUTPUT parameter, but I've only ever sucked that into another SP, rather than into a VB variable.

Any ideas, anyone?

James

I'm now going to have a play with the Snag56 code!

 

[JamesHardiman]

Wow! That hurt my brain! But I think I got there in the end. This is what I've ended up with:

SELECT IDENTITY(int, 1,1) AS ID_Num, GuestId into #gapper from tblGuests

select top 1 ID_Num from #gapper where ID_Num < GuestId

if @@ROWCOUNT = 0 select max(ID_Num) + 1 as ID_Num from #gapper

drop table #gapper

What I still haven't quite grasped is that I needed to make the where clause: ID_Num < GuestId, whereas your original had <>.

That's one very powerful snippet of code! I think I'm going to go home and lie down in a dark room!

...and my other question, about getting the information back into a VB variable is still there!

Thanks, Snag56, and James Lean--my SQL education has moved forward a whole chunk.

James