alter table character add GensContribution int
alter table character add GensRank int
alter table character add GensFamily int
exec GensRankingUpdate 1
create Procedure [dbo].[GensRankingUpdate]
@GensFam int
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #RankTmp (
RowNumber int IDENTITY (1, 1),
Name nvarchar(10),
GensContribution int
)
INSERT #RankTmp (Name,GensContribution)
SELECT Name, GensContribution
FROM Character
WHERE GensFamily = @GensFam
ORDER BY GensContribution Desc;
UPDATE Character
SET Character.GensRank = #RankTmp.RowNumber
FROM Character
INNER JOIN #RankTmp
ON Character.Name collate Chinese_PRC_CI_AS = #RankTmp.Name collate Chinese_PRC_CI_AS
DROP TABLE #RankTmp
SET NOCOUNT OFF
End

Advertisements