cs_system_updateuserpostrank.prc

community server 源码

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_system_UpdateUserPostRank]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_system_UpdateUserPostRank]
GO





create      procedure [dbo].cs_system_UpdateUserPostRank
(
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN
DECLARE @Usercount int
DECLARE @LoopCounter int

	SET NOCOUNT ON

	CREATE Table #PostRank (
	  Rank int IDENTITY (1, 1) NOT NULL,
	  UserID int
	)

	-- Select into temp table
	INSERT INTO #PostRank (UserID)
	SELECT TOP 500
		P.UserID
	FROM
		cs_UserProfile P
	Where SettingsID = @SettingsID
	ORDER BY
		P.TotalPosts DESC

	-- How many users did we select?
	SELECT @Usercount = count(*) FROM #PostRank


	-- First clear all the users
	UPDATE 
		cs_UserProfile
	SET 
		PostRank = 0x0
	WHERE 
		PostRank > 0 and SettingsID = @SettingsID

	-- Top 10
	UPDATE
		cs_UserProfile
	SET
		PostRank = 0x01
	WHERE
		UserID in (SELECT UserID FROM #PostRank WHERE Rank < 11 and SettingsID = @SettingsID) and SettingsID = @SettingsID

	-- Top 25
	UPDATE
		cs_UserProfile
	SET
		PostRank = 0x02
	WHERE
		UserID in (SELECT UserID FROM #PostRank WHERE Rank > 10 AND Rank < 26 and SettingsID = @SettingsID) and SettingsID = @SettingsID


	-- Top 50
	UPDATE
		cs_UserProfile
	SET
		PostRank = 0x04
	WHERE
		UserID in (SELECT UserID FROM #PostRank WHERE Rank > 25 AND Rank < 51 and SettingsID = @SettingsID) and SettingsID = @SettingsID


	-- Top 75
	UPDATE
		cs_UserProfile
	SET
		PostRank = 0x08
	WHERE
		UserID in (SELECT UserID FROM #PostRank WHERE Rank > 50 AND Rank < 76 and SettingsID = @SettingsID) and SettingsID = @SettingsID

	-- Top 100
	UPDATE
		cs_UserProfile
	SET
		PostRank = 0x10
	WHERE
		UserID in (SELECT UserID FROM #PostRank WHERE Rank > 75 AND Rank < 101 and SettingsID = @SettingsID) and SettingsID = @SettingsID

	-- Top 150
	UPDATE
		cs_UserProfile
	SET
		PostRank = 0x20
	WHERE
		UserID in (SELECT UserID FROM #PostRank WHERE Rank > 100 AND Rank < 151 and SettingsID = @SettingsID) and SettingsID = @SettingsID

	-- Top 200
	UPDATE
		cs_UserProfile
	SET
		PostRank = 0x40
	WHERE
		UserID in (SELECT UserID FROM #PostRank WHERE Rank > 150 AND Rank < 200 and SettingsID = @SettingsID) and SettingsID = @SettingsID


	-- Top 500
	UPDATE
		cs_UserProfile
	SET
		PostRank = 0x80
	WHERE
		UserID in (SELECT UserID FROM #PostRank WHERE Rank > 200 and SettingsID = @SettingsID) and SettingsID = @SettingsID

END





























GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_system_UpdateUserPostRank] to public
go