Stored Procedure Properties

Name	Value
Owner	dbo
Created	2004-05-31
Startup	False
Encrypted	False

Creation Options

Name	Value
QUOTED_IDENTIFIER	ON
ANSI_NULLS	ON

Parameters

Name	DataType	Length	Type

Total: 0 parameter(s)

SQL

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



--select top 500 username ,attributes,totalposts from users order by totalposts desc

--exec Statistics_ResetTopPosters
CREATE  procedure Statistics_ResetTopPosters
AS
BEGIN
DECLARE @Usercount int
DECLARE @LoopCounter int

	SET NOCOUNT ON

	CREATE Table #Top500Users (
	  Rank int IDENTITY (1, 1) NOT NULL,
	  UserName nvarchar(50)
	)

	-- Select into temp table
	INSERT INTO #Top500Users (UserName)
	SELECT TOP 500
		UserName
	FROM
		Users
	ORDER BY
		TotalPosts DESC

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

	-- Set the loop counter
	SET @LoopCounter = 1

	WHILE (@LoopCounter <= @Usercount)
	BEGIN
		-- clear the flags for top 25, 50, and 100
		UPDATE Users
		SET Attributes = (convert(int, Attributes) & 0xFFFFFFF3)
		WHERE Username = (SELECT Username FROM #Top500Users WHERE Rank = @LoopCounter)

		-- top 25 poster
		IF (@LoopCounter < 26)
			UPDATE Users
			SET Attributes = (Attributes ^ 4)
			WHERE Username = (SELECT Username FROM #Top500Users WHERE Rank = @LoopCounter)

		-- top 50 poster
		IF (@LoopCounter > 25) AND (@LoopCounter < 51)
			UPDATE Users
			SET Attributes = (Attributes ^ 8)
			WHERE Username = (SELECT Username FROM #Top500Users WHERE Rank = @LoopCounter)

		-- top 100 poster
		IF (@LoopCounter > 50) AND (@LoopCounter < 101)
		BEGIN
			UPDATE Users
			SET Attributes = (Attributes ^ 16)
			WHERE Username = (SELECT Username FROM #Top500Users WHERE Rank = @LoopCounter)
		END
	
		-- increment the coutner
		SET @LoopCounter = @LoopCounter + 1
	END
END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO