get the last Id in an interval in Database

Question

0.00/5 (No votes)

See more:

hi all
i have a a table in database saving in it a lot of data and i design this table to be from the Id 500 to 1000 is for teacher names for example and from 1001 to 2000 for student names.
Now, i want to make a stored procedure to add new teacher , how can i tell him go to this interval and get the last id of it and insert after it???

Can any One helps me in it, please????????

Thanks

Posted 15-Mar-12 1:19am

moon2011

Add a Solution

Comments

Om Prakash Pant 15-Mar-12 7:24am

this is not a good design..

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

rajdeep kumar · Answer 1 · 2012-03-15T01:43:00

Solution 3

hi there....

if u can change ur DB design change it otherwise take parameter from SP as ROLE(teacher or student) and then insert it based on condition) take last id from @@identity and then insert value with next id...

Hope this helps u....

Posted 15-Mar-12 1:43am

rajdeep kumar

Dylan Morley · Answer 2 · 2012-03-15T01:29:00

This is a bad design in my opinion. You should let the database ID be auto generated whenever a record is inserted, you shouldn't be trying to control the unique identifiers yourself.

Instead, you should create 2 tables that look similar to this.

UserInformation Table
Id, Name, UserTypeId
1, "Dylan", 1
2, "Dylans Teacher", 2

User Type Table
Id, Description
1, "Student"
2, "Teacher"

Your UserInformation table should take an identifier for the 'User Type' which will describe the kind of user, e.g. student or teacher. This should be a foreign key to the primary key in 'User Type' table

Now, you don't have to worry about inserting within a particular range of IDs, you just add another field that describes the user. If you need to add more user types (e.g 'Professor' or 'Exchange Student'), simple - just add the record to user types and you can start recording against it.

*dan!sh* · Answer 3 · 2012-03-15T01:27:00

Solution 1

You basically need to redesign your database schema. This is not a good way to store values. You should have separate tables to store teacher and student records.

Posted 15-Mar-12 1:27am

dan!sh