Click here to Skip to main content
14,430,134 members
Rate this:
Please Sign up or sign in to vote.
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
Comments
Om Prakash Pant 15-Mar-12 7:24am
   
this is not a good design..
Rate this:
Please Sign up or sign in to vote.

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.
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

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.
   
Rate this:
Please Sign up or sign in to vote.

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....
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100