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 |
|
Disco Patrick
Starting Member
18 Posts |
 Posted - 2009-09-29 : 10:47:11
|
| I have a few tables in my database which are intermediate tables between a many-to-many relationship (I forget the technical name right now)...For example, I have a table called Person and a table called Club. A club can have more than one person as a member, and a person can be a member of more than one club. Therefore my intermediate table is called PersonInClub and has columns PersonID, ClubID, and finally PersonOrder - an integer defining the order of the person in the club.I already have a stored procedure for re-ordering people in the club - this takes a PersonID and a NewOrder as parameters, moves the Person to the new postion and then shuffles the other people around so that the PersonOrder column always counts 1, 2, 3, 4, etc for each club.What I'm looking for is the best way to add a new person to a club, and have their PersonOrder automatically set to the next number up.A simple looking solution would be to set a DEFAULT on the PersonOrder column that calls a function like udf_GetNextPersonOrderInClub(ClubID), gets the MAX(PersonOrder) WHERE ClubID = @ClubID and then returns that number plus 1. However, this would require that the function can accept the ClubID that is being inserted and I'm not sure that's possible - is it?If that's not possible then I'll look at triggers, I just wanted to rule out a DEFAULT as an option... |
|
|
Disco Patrick
Starting Member
18 Posts |
Posted - 2009-09-29 : 11:01:16
|
Have just realised my function can't be:SELECT MAX(PersonOrder) FROM PersonInClubWHERE ClubID = @ClubID but must instead be:SELECT COUNT(*) FROM PersonInClubWHERE ClubID = @ClubID ...to allow for clubs that do not yet have any people. Therefore, if a club has zero people, the function will take that number and add 1, and so the PersonOrder of the new record will be 1 - the first person in the club. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-09-29 : 13:01:11
|
| if you're using sql 2005 you can generate this value at runtime using ROW_NUMBER() function. just use value COUNT(PersonID) OVER (PARTITION BY ClubID) + 1 as the value for PersonOrder in insert statement |
|
|
|
|
|
|
|
|
Best way to populate an [Order] column