Stored procedure question - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Stored procedure question

Author

Topic

rejwan
Starting Member

4 Posts

Posted - 2007-11-27 : 14:16:29

Hi everyone, first post for me :)

I'm running a game called WarGames 2.0 (http://www.wargames2.net).
Now - I'm using MSSQL 2005 workgroup edition, and I have this horrible player rank calculation that has to change.

It's something like this:
First cursor - Runs over all the players in the game and recalculates their score.
Second cursor - Runs again over all the players, arranged by their new score to set their rank.

I'm pretty sure the first cursor is a must, since I execute a stored procedure for every player in the game.
The second cursor is what I'm trying to get rid of.

This command:
SELECT ROW_NUMBER() OVER (ORDER BY power DESC) Rank FROM [countries]
Gives exactly the second cursor's result, unfortunately, I don't know how (Or even if) it's possible to use it to update the table.

Any help is welcomed :)

Ron Rejwan

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-27 : 14:24:58

update a
set a.MyCol = row_Number() over (Order by a.Col1)
from
Mytable a

rejwan
Starting Member

4 Posts

Posted - 2007-11-27 : 15:03:37

Already tried that:

Msg 4108, Level 15, State 1, Line 1
Windowed functions can only appear in the SELECT or ORDER BY clauses.

Ron Rejwan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-28 : 01:29:36

[code]update a
set mycol = b.row_no
from countries a
inner join
(
select pk, row_no = row_Number() over (Order by power DESC)
from countries
) b on a.pk = b.pk[/code]

KH
[spoiler]Time is always against us[/spoiler]

rejwan
Starting Member

4 Posts

Posted - 2007-11-28 : 02:03:46

Hi Khtan, I've eventually used this (Which works perfectly):

UPDATE D SET
Rank = rownum
FROM
(
SELECT rank, ROW_NUMBER() OVER (ORDER BY power DESC) AS rownum
FROM countries
) AS D

Any idea which one is more efficient?

Ron Rejwan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-28 : 03:44:41

run it and check the execution plan

KH
[spoiler]Time is always against us[/spoiler]

rejwan
Starting Member

4 Posts

Posted - 2007-11-28 : 05:42:22

Seems to be split 50%/50% with the one I used and the one you gave me khtan!
Thanks for the help!

Ron Rejwan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources