4W6aA87g

1USE [KN_Online]
2GO
3/****** Object:  StoredProcedure [dbo].[USER_KNIGHTS_RATING_UPDATE]    Script Date: 04/25/2011 01:25:59 ******/
4SET ANSI_NULLS OFF
5GO
6SET QUOTED_IDENTIFIER OFF
7GO
8
9/****** Object:  Stored Procedure dbo.USER_KNIGHTS_RATING_UPDATE    Script Date: 6/6/2006 6:03:34 PM ******/
10
11-- Created By Sungyong 2003. 1. 22
12
13ALTER PROCEDURE [dbo].[USER_KNIGHTS_RATING_UPDATE] AS
14
15SET NOCOUNT ON
16
17if exists (select * from sysobjects where id = object_id(N'[dbo].[user_rating_temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
18drop table [dbo].[user_rating_temp]
19CREATE TABLE [dbo].[user_rating_temp] (
20	[nRank] [int] IDENTITY (1, 1) NOT NULL ,
21	[strUserID] [char] (21) NULL,
22	[strKnightsName] [char] (21) NULL ,
23	[nLoyalty] [int] NULL ,
24) ON [PRIMARY]
25
26-- karus user
27INSERT INTO user_rating_temp SELECT   TOP 100 USERDATA.strUserId, KNIGHTS.IDName, USERDATA.Loyalty
28FROM      USERDATA INNER JOIN
29                KNIGHTS ON USERDATA.Knights = KNIGHTS.IDNum
30WHERE   (USERDATA.Nation = 1) and (USERDATA.City <> 255 )
31ORDER BY USERDATA.Loyalty DESC
32
33-- elmo user
34INSERT INTO user_rating_temp SELECT   TOP 100 USERDATA.strUserId, KNIGHTS.IDName, USERDATA.Loyalty
35FROM      USERDATA INNER JOIN
36                KNIGHTS ON USERDATA.Knights = KNIGHTS.IDNum
37WHERE   (USERDATA.Nation = 2) and (USERDATA.City <> 255 )
38ORDER BY USERDATA.Loyalty DESC
39
40CREATE  INDEX [IX_rating_rank] ON [dbo].[user_rating_temp]([nRank]) ON [PRIMARY]
41
42-- user_knights_rank table update
43
44DECLARE @strCharID char(21)
45DECLARE @strKnightsID char(21)
46DECLARE @nIndex smallint
47DECLARE @nLoyalty int
48
49SET @strCharID = NULL
50SET @strKnightsID = NULL
51SET @nIndex = 0
52SET @nLoyalty = 0
53
54DECLARE job1 CURSOR FOR
55
56SELECT nRank, strUserID, strKnightsName, nLoyalty FROM user_rating_temp
57
58OPEN job1
59FETCH NEXT FROM job1
60INTO @nIndex, @strCharID, @strKnightsID, @nLoyalty
61WHILE @@fetch_status = 0 
62BEGIN
63	IF @nIndex < 101
64		UPDATE USER_KNIGHTS_RANK SET strKarusUserID = @strCharID, strKarusKnightsName = @strKnightsID, nKarusLoyalty = @nLoyalty WHERE shIndex = @nIndex
65	ELSE 
66		UPDATE USER_KNIGHTS_RANK SET strElmoUserID = @strCharID, strElmoKnightsName = @strKnightsID, nElmoLoyalty = @nLoyalty WHERE shIndex = @nIndex-100
67	
68	FETCH NEXT FROM job1
69	INTO @nIndex, @strCharID, @strKnightsID, @nLoyalty
70END
71CLOSE job1
72DEALLOCATE job1
73
74if exists (select * from sysobjects where id = object_id(N'[dbo].[user_rating_temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
75drop table [dbo].[user_rating_temp]
76
77SET NOCOUNT OFF