July 2, 2004 at 2:03 pm
Hi all,
This is something I have done for smaller tables in Java, but I would really prefer to do this in a stored proc.
I have a table with 200,000 rows of hockey stats which are linked to about 30,000 players. Volunteers enter stats, and they often get entered in random order. Therefore, when I present a players stats on a page, I sort by season, and everything is ok. But, if a player was traded mid-season, and the stats get entered in the wrong order, it leads people to believe that the player may have started the season with the team they actually finished with simply because the order in which they are displayed.
To remedy this, I have added an integer field that will represent the "sequence" of appearance. For most players, these values will remain unchanged, so I want a script that will simply select all distinct player IDs and then loop over the results and then select the stats for each player (sorted by season ASC) and assign each row of stats a sequential number from 1 to whatever.
I wrote a quick java program to do it but it took 15 minutes to complete! So I would like to do it in a stored proc. if possible. Each stats row has a unique key and obviously has the player ID as well.
Any info appreciated.
JW
July 5, 2004 at 8:00 am
This was removed by the editor as SPAM
July 5, 2004 at 9:36 am
I am not sure I totally understand you schema.
However, you may want to simply add a field into the table as an identity column. SQL Server will auto increment that field as rows are inserted into the table.
Another option:
Create a stored procedure that does this exactly in the sequential order you are looking to achieve. What you need to do is use a cursor. This will let you fetch each row, do your calculation, and then perform an update to the row.
Look at the SQL Server help manual for cursors and you will see some examples on how to declare them and interate through them. If you are familiar with recordsets in ADO (not sure how JAVA exposes data from a database), you will see it is fairly similar aside from syntax.
July 5, 2004 at 10:44 am
JW this is a wag but what you probably need is two stored procedures (or one that returns multiple recordsets) to retrieve all of the information on a specific player, something along the line of, I don't think you really are looking for a list of all players and their stats in a single query (or are you?):
create procedure usp_get_player_info
@player_id int
as
select * (field list is better) from players where player_id = @player_id
go
create procedure usp_get_player_stats
@player_id int
as
select * from player_stats where player_id = @player_id order by year , sequence
go
If you're up to handling multiple recordsets in your web page (e.g. a "nextrecordset") you could use a single stored procedure like this:
create procedure usp_get_player_stats
@player_id int
as
select * from players where player_id = @player_id
select * from player_stats where player_id = @player_id order by year, sequence
go
In your client the command will be something like "exec dbname.dbo.usp_get_player_stats @player_id = 1"...
Joe
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply