[Resolved!] Getting list of avail. unique numbers? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

[Resolved!] Getting list of avail. unique numbers?

Author

Topic

DTFan
Yak Posting Veteran

52 Posts

Posted - 2005-12-22 : 08:26:19

First off let me apologize if this sounds really confusing (I hope it doesn't).

Not really sure how to go about this. Here is the situation. In my little test project I've got a studentclass database. Basically I am creating an application to keep track of students, classes, professors, schedules, etc. I decided to add "Clubs" to the list of things I'm going to track/store/administer. So, now you've got 3 tables to work together (tblStudent, tblClub, tblClubMembership).

In tblClubMembership I want to assign unique "memIDs" (not based on tblStudent.studID). Basically it's a 4 digit code that will be that students membershipID for that specific club. Unique numbers only which start at 1000 and go to 9999. I thought about making it a auto-incrementing number (like a primary key) but I wanted to try something different.

I set it up so that the user will be able to enter a number of their choice and, if it's not taken, then they are inserted. Otherwise it will give them a message saying that number is already taken and they have to select another one. Like when you try signing up for an email account with Yahoo and the userID you select is already taken and they suggest an alternative. So, instead of "myname@yahoo.com" it suggests "myname1234@yahoo.com".

Right now if the ID is already taken it gives them the message but doesn't return the next ID that would be available (for example, if 1010 is taken and they put that in, the system should inform them that 1011 is available). It doesn't automatically do the insert it just tells them that the number is already taken. Right now it's just a query to check for the existance of the number they selected. I'm not sure on how to get (nonetheless return) the next available number.

But what I really want to accomplish is a multiple "insert" of new students for a club. So, lets say the administrator has to enroll 10 students into the "Save the Earth" club. Instead of them having to enter each student individually, they can select the 10 students from a list of students, pick a "starting" memID, and then have the program enter each student in order. And hopefully be able to do so with consecutive numbers (obviously skipping over numbers already used).

Wow, I'm sorry that sounds so confusing. I've got the form built (visual basic 2003) to handle the insertion of a single student (with an error messagebox when it can't do it because the number already exists). I've also got it setup so they can select multiple students (listbox) to do a multiple insert stored procedure. But it's the stored procedure that I'm having a problem with.

Let me give some example data to show what I am trying to accomplish.

Here is a script that creates the 3 tables and some sample data in each one:

*********************************


CREATE TABLE [tblClub] (
  [clubID] int IDENTITY(1, 1) NOT NULL,
  [clubName] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('') NOT NULL
)
ON [PRIMARY]
GO

EXEC sp_addextendedproperty 'MS_Description', N'primary key for the club', 
  'user', 'dbo', 'table', 'tblClub', 'column', 'clubID'
GO

EXEC sp_addextendedproperty 'MS_Description', N'name of the club', 'user', 
  'dbo', 'table', 'tblClub', 'column', 'clubName'
GO

CREATE TABLE [tblClubMembership] (
  [cmemID] bigint IDENTITY(1, 1) NOT NULL,
  [clubID] int DEFAULT (1) NOT NULL,
  [studID] int DEFAULT (1) NOT NULL,
  [memID] smallint DEFAULT (1000),
  [joinDate] smalldatetime DEFAULT (getdate()) NOT NULL,
  [leaveDate] smalldatetime DEFAULT ('1/1/1900') NOT NULL
)
ON [PRIMARY]
GO

EXEC sp_addextendedproperty 'MS_Description', N'foreign key to tblClub', 
  'user', 'dbo', 'table', 'tblClubMembership', 'column', 'clubID'
GO

EXEC sp_addextendedproperty 'MS_Description', N'foreign key to tblStudent', 
  'user', 'dbo', 'table', 'tblClubMembership', 'column', 'studID'
GO

EXEC sp_addextendedproperty 'MS_Description', 
  N'4 digit code so students don''t have to use studID', 'user', 'dbo', 'table', 
  'tblClubMembership', 'column', 'memID'
GO

EXEC sp_addextendedproperty 'MS_Description', N'date they joined', 'user', 
  'dbo', 'table', 'tblClubMembership', 'column', 'joinDate'
GO

EXEC sp_addextendedproperty 'MS_Description', N'date they left the club', 
  'user', 'dbo', 'table', 'tblClubMembership', 'column', 'leaveDate'
GO

CREATE TABLE [tblStudent] (
  [studID] int IDENTITY(1, 1) NOT NULL,
  [majID] tinyint NOT NULL,
  [stypeID] tinyint NOT NULL,
  [studFName] varchar(35) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('') NOT NULL,
  [studLName] varchar(35) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('') NOT NULL,
  [studEmail] varchar(75) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('') NOT NULL
)
ON [PRIMARY]
GO

EXEC sp_addextendedproperty 'MS_Description', N'major ID foreign key', 'user', 
  'dbo', 'table', 'tblStudent', 'column', 'majID'
GO

EXEC sp_addextendedproperty 'MS_Description', N'student type', 'user', 'dbo', 
  'table', 'tblStudent', 'column', 'stypeID'
GO

EXEC sp_addextendedproperty 'MS_Description', N'students first name', 'user', 
  'dbo', 'table', 'tblStudent', 'column', 'studFName'
GO

EXEC sp_addextendedproperty 'MS_Description', N'students last name', 'user', 
  'dbo', 'table', 'tblStudent', 'column', 'studLName'
GO

EXEC sp_addextendedproperty 'MS_Description', N'students email address', 'user', 
  'dbo', 'table', 'tblStudent', 'column', 'studEmail'
GO

SET IDENTITY_INSERT [tblClub] ON
GO

INSERT INTO [tblClub] ([clubID], [clubName])
VALUES 
  (1, 'No Club')
GO

INSERT INTO [tblClub] ([clubID], [clubName])
VALUES 
  (2, 'Astronomy')
GO

INSERT INTO [tblClub] ([clubID], [clubName])
VALUES 
  (3, 'Chess')
GO

INSERT INTO [tblClub] ([clubID], [clubName])
VALUES 
  (4, 'PoliSci Leaders')
GO

INSERT INTO [tblClub] ([clubID], [clubName])
VALUES 
  (5, 'Save The Earth')
GO

INSERT INTO [tblClub] ([clubID], [clubName])
VALUES 
  (6, 'PETA')
GO

INSERT INTO [tblClub] ([clubID], [clubName])
VALUES 
  (7, 'Spanish')
GO

INSERT INTO [tblClub] ([clubID], [clubName])
VALUES 
  (8, 'German')
GO

INSERT INTO [tblClub] ([clubID], [clubName])
VALUES 
  (9, 'S.P.E.W.')
GO

SET IDENTITY_INSERT [tblClub] OFF
GO

COMMIT
GO

SET IDENTITY_INSERT [tblClubMembership] ON
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
  [joinDate], [leaveDate])
VALUES 
  (1, 1, 1, 1000, '1/1/1900', '1/1/1900')
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
  [joinDate], [leaveDate])
VALUES 
  (2, 4, 8, 1205, '4/27/2004', '1/1/1900')
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
  [joinDate], [leaveDate])
VALUES 
  (3, 7, 12, 1105, '8/12/2005', '1/1/1900')
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
  [joinDate], [leaveDate])
VALUES 
  (4, 5, 14, 1206, '9/15/2005', '1/1/1900')
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
  [joinDate], [leaveDate])
VALUES 
  (5, 2, 2, 1800, '10/10/2005', '1/1/1900')
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
  [joinDate], [leaveDate])
VALUES 
  (6, 8, 5, 1305, '11/10/2004', '9/18/2005')
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
  [joinDate], [leaveDate])
VALUES 
  (7, 6, 9, 1417, '4/12/2004', '6/12/2004')
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
  [joinDate], [leaveDate])
VALUES 
  (8, 3, 6, 1703, '10/11/2005', '1/1/1900')
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
  [joinDate], [leaveDate])
VALUES 
  (9, 7, 11, 2508, '5/18/2005', '1/1/1900')
GO

INSERT INTO [tblClubMembership] ([cmemID], [clubID], [studID], [memID], 
  [joinDate], [leaveDate])
VALUES 
  (10, 5, 12, 1109, '8/12/2005', '1/1/1900')
GO

SET IDENTITY_INSERT [tblClubMembership] OFF
GO

COMMIT
GO

SET IDENTITY_INSERT [tblStudent] ON
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], 
  [studLName], [studEmail])
VALUES 
  (1, 1, 1, 'Temp', 'Student', 'no@no.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], 
  [studLName], [studEmail])
VALUES 
  (2, 9, 1, 'Ali', 'Larter', 'ali@finaldestination.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], 
  [studLName], [studEmail])
VALUES 
  (3, 9, 1, 'Amy', 'Smart', 'amy@butterflyeffect.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], 
  [studLName], [studEmail])
VALUES 
  (4, 8, 2, 'Katie', 'Holmes', 'katie@thecreek.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], 
  [studLName], [studEmail])
VALUES 
  (5, 9, 1, 'Jordana', 'Brewster', 'jordana@fastandfurious.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], 
  [studLName], [studEmail])
VALUES 
  (6, 7, 3, 'Anna', 'Kournikova', 'anna@tennis.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], 
  [studLName], [studEmail])
VALUES 
  (7, 2, 2, 'Rhona', 'Mitra', 'rhona@niptuck.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], 
  [studLName], [studEmail])
VALUES 
  (8, 5, 1, 'Julie', 'Bowen', 'jbowen@bostonlegal.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], 
  [studLName], [studEmail])
VALUES 
  (9, 2, 4, 'Lake', 'Bell', 'lakeb@surface.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], 
  [studLName], [studEmail])
VALUES 
  (10, 4, 3, 'Keira', 'Knightly', 'keira@thejacket.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], 
  [studLName], [studEmail])
VALUES 
  (11, 7, 2, 'Jennifer', 'Morrison', 'jen@house.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], 
  [studLName], [studEmail])
VALUES 
  (12, 8, 3, 'Evangeline', 'Lilly', 'elilly@lost.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], 
  [studLName], [studEmail])
VALUES 
  (13, 9, 4, 'Jennifer', 'Finnigan', 'jennifer@closetohome.com')
GO

INSERT INTO [tblStudent] ([studID], [majID], [stypeID], [studFName], 
  [studLName], [studEmail])
VALUES 
  (14, 6, 2, 'Mila', 'Kunis', 'mila@that70sshow.com')
GO

SET IDENTITY_INSERT [tblStudent] OFF
GO

COMMIT
GO

ALTER TABLE [dbo].[tblClub]
ADD CONSTRAINT [PK_tblClub] PRIMARY KEY CLUSTERED ([clubID])
ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblClubMembership]
ADD CONSTRAINT [PK_tblClubMembership] PRIMARY KEY CLUSTERED ([cmemID])
ON [PRIMARY]
GO

CREATE INDEX [tblClubMembership_idx1] ON [dbo].[tblClubMembership]
  ([clubID])
ON [PRIMARY]
GO

CREATE INDEX [tblClubMembership_idx2] ON [dbo].[tblClubMembership]
  ([studID])
ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblClubMembership]
ADD CONSTRAINT [tblClubMembership_uq] UNIQUE ([memID])
ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblStudent]
ADD CONSTRAINT [PK_tblStudent] PRIMARY KEY CLUSTERED ([studID])
ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblClubMembership]
ADD CONSTRAINT [tblClubMembership_fk1] FOREIGN KEY ([clubID]) 
  REFERENCES [dbo].[tblClub] ([clubID])
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

ALTER TABLE [dbo].[tblClubMembership]
ADD CONSTRAINT [tblClubMembership_fk2] FOREIGN KEY ([studID]) 
  REFERENCES [dbo].[tblStudent] ([studID])
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

*********************************

So, right now a query would return something like this:



PoliSci Leaders     Julie Bowen          1205
Spanish             Evangeline Lilly     1105
Save the Earth      Mila Kunis           1206
Astronomy           Ali Larter           1800
German              Jordana Brewster     1305
PETA                Lake Bell            1417
Chess               Anna Kournikova      1703
Spanish             Jennifer Morrison    2508
Save the Earth      Evangeline Lilly     1109

So say I tried to insert 10 students (in this case students 1 - 10) into a club (multiple insert), and the administer said to start with memID 1100. What should happen is an insert is done (let's say for the Save the Earth club (#5)) and the ID's assigned should be:


clubID    studID  memID

  5         2      1100
  5         3      1101
  5         4      1102
  5         5      1103
  5         6      1104
  5         7      1106
  5         8      1107
  5         9      1108
  5         10     1110
  5         11     1111

Notice the example skipped 1105 and 1109 because they are already used. If the end user had chosen to start at 2505, then 2505 - 2515 would be used with 2508 being skipped. If the user is doing a "multiple" insert I'm not concerned about letting them know that the number they picked is not available or that certain numbers needed to be skipped. But if they are doing a single insert (just adding one student to a club), then I'd like to return the next available memID if it [the one they picked] is not available but NOT do the insert (in case for whatever reason they want to pick a different number).

Is this even possible? Any idea's on how to proceed? I've looked into Min() but can't figure out how to look for a number that's not there. I figure I'll have to pass in a couple of parameters (@in_intStartingMemID, @in_intStudID, @in_NumToAdd (for looping?), @in_intClubID).

Thank you in advance for any help/suggestions/tutorials/samples that you can give. This is something that is WAYYYY over my head but something I think would be very cool to know and incorporate. So thanks again.

PS - sorry for the long, confusing post

DTFan
Ever-hopeful programmer-in-training

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-22 : 08:39:52

See this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58310
It should provide you some help to your problem

-----------------
[KH]
Learn something new everyday

DTFan
Yak Posting Veteran

52 Posts

Posted - 2005-12-22 : 09:08:37

quote:
Originally posted by khtan

See this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58310
It should provide you some help to your problem

khtan - thanks for that. I had done some searching but those didn't turn up. I have to admit I just got cross-eyed and confused reading the posts in that thread but it definitely gives me something to start playing with. Thanks for the link.

DTFan
Ever-hopeful programmer-in-training

DTFan
Yak Posting Veteran

52 Posts

Posted - 2005-12-28 : 07:46:46

Well, I thought I had it working but now I'm running into 2 problems. Actually, the second problem isn't appropriate for this thread (as it deals with running the actual "insert" and not getting the list of available ID's) so I will limit it to just one question.

I read a number of threads concerning unique ID's and available numbers, etc. I though that this was working and then I realized it was working for all except the first number. Here's my stored procedure thus far (without the stuff for the insert):

CREATE PROCEDURE admin_sp_GetMemIDs
(
     @in_intNumToAdd INTEGER,
     @in_intStartingMemID INTEGER
)

AS

     SET NOCOUNT ON;

BEGIN

SET ROWCOUNT @in_intNumToAdd

     SELECT NUMBER
     FROM dbo.F_TABLE_NUMBER_RANGE(@in_intStartingMemID, 9999)
     WHERE NUMBER NOT IN
          (
               SELECT  memID
               FROM    tblClubMembership
               WHERE   memID > @in_intStartingMemID
          )
     ORDER BY NUMBER ASC

SET ROWCOUNT 0

END

At first look I thought this was working but here's the problem. Using the sample data I provided above, let's say the user wanted to add 5 users using a starting MemID of 1108. What they would get is:

Which is perfect because it skips 1109 (in the sample data that is assigned to Evangeline Lilly of the Save The Earth club). That is exactly as it should be. But, assuming the same situation, but using starting MemID of 1105, this is what is returned:

It still skipped 1109 (like it should), but it did include 1105 (which it shouldn't). The procedure seems to be returning the startingMemID regardless of whether it is already used or not. The code that I'm using I got from this post:

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685[/url]

And like it said, it's working like a champ. I know that it's something I'm doing wrong but I don't know where exactly I'm going wrong.

Is there anything you can see that would cause this to happen?

Thank you in advance for any and all help that you can give. It is greatly appreciated.

DTFan
Ever-hopeful programmer-in-training

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-28 : 09:17:28

Sorry if this is way off base but all I really read is your last post

>>all except the first number
Do you just need a greater than or equal to? (below in red)


     WHERE NUMBER NOT IN
          (
               SELECT  memID
               FROM    tblClubMembership
               WHERE   memID >= @in_intStartingMemID
          )

Be One with the Optimizer
TG

DTFan
Yak Posting Veteran

52 Posts

Posted - 2005-12-28 : 10:01:06

quote:
Originally posted by TG

Sorry if this is way off base but all I really read is your last post

WHERE memID >= @in_intStartingMemID

[/code]

Be One with the Optimizer
TG

That did it. Thank you for that.

So what that is doing is saying give me all the numbers that are NOT in tblClubMembership and includes the initial number. Whereas what I had before was simply not including the initial number in the second select so therefore it was NOT in the recordset. It makes sense but I don't think I would have figured that out b/c it is kinda of a round-about method (to me, at least).

Thank you again. You have alleviated one of my headaches

Time to mark this thread Resolved!

DTFan
Ever-hopeful programmer-in-training

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources