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.
| Author |
Topic |
|
petebob796
Starting Member
35 Posts |
 Posted - 2008-07-16 : 06:01:05
|
| I work for a small college and am currently updating lots of separated systems into one easier to use less error prone web site. I am currently just working on login credentials stuff for the next academic year (for students). The way the old system would work would be in our student db we have a unique student id each student is enrolled on one or more courses for which we have the start and end date's. We currently just look for anyone enrolled in the appropriate year and create their user ID and email address as their student id. IT get sent some CSV and BAT files to do their bit on a regular basis. This is not very user friendly for the students and I am hoping to change it to Forename + Surname.However if two people have the same name I obviously need to differentiate probably by appending a number to the end. Any ideas how to append an appropriate number if the forename + surname already exist. Hopefully in SQL as I would prefer to wrap it all into a stored procedure to keep the code clean. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-07-16 : 06:04:09
|
Use an IDENTITY column.It it almost never a good idea to use names as unique key. What happens if one gets married or change the last name for any reason?
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
petebob796
Starting Member
35 Posts |
Posted - 2008-07-16 : 06:39:12
|
| I think you miss understood the unique field is student_id however for windows logins and emails we want to have them as student forename and surname.Anyway fixed it now doing a search for the appropriate surname/forename counting the number of records returned. |
|
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2008-07-16 : 07:33:15
|
Ah - miss understood - our ever-present mistress  Ryan Randall Solutions are easy. Understanding the problem, now, that's the hard part. |
|
|
|
|
|
|
Generate Unique User ID's