Stored Procedure Properties

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

Creation Options

Name	Value
QUOTED_IDENTIFIER	OFF
ANSI_NULLS	OFF

Parameters

Name	DataType	Length	Type
@PageIndex	int	4	INPUT
@PageSize	int	4	INPUT
@SortBy	int	4	INPUT
@SortOrder	bit	1	INPUT
@UserNameBeginsWith	nvarchar	2	INPUT

Total: 5 parameter(s)

SQL

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO






CREATE                      PROCEDURE forums_GetAllUsers
(
	@PageIndex int,
	@PageSize int,
	@SortBy int,
	@SortOrder bit,
	@UserNameBeginsWith nvarchar(1)
)
 AS
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int

-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

-- Create a temp table to store the select results
CREATE TABLE #PageIndexForUsers 
(
	IndexID int IDENTITY (1, 1) NOT NULL,
	Username nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
)	

-- Special case depending on what the user wants and how they want it ordered by

-- Sort by Date Joined
IF @SortBy = 0 AND @SortOrder = 0
    IF @UserNameBeginsWith IS NULL
	    INSERT INTO #PageIndexForUsers (Username)
	    SELECT Username FROM Users WHERE Approved = 1 AND DisplayInMemberList = 1 ORDER BY DateCreated
    ELSE
	    INSERT INTO #PageIndexForUsers (Username)
	    SELECT Username FROM Users WHERE Approved = 1 AND DisplayInMemberList = 1 AND LEFT(UserName, 1) = @UserNameBeginsWith ORDER BY DateCreated
ELSE IF @SortBy = 0 AND @SortOrder = 1
    IF @UserNameBeginsWith IS NULL
	    INSERT INTO #PageIndexForUsers (Username)
	    SELECT Username FROM Users WHERE Approved = 1 AND DisplayInMemberList = 1  ORDER BY DateCreated DESC
    ELSE
	    INSERT INTO #PageIndexForUsers (Username)
	    SELECT Username FROM Users WHERE Approved = 1 AND DisplayInMemberList = 1  AND LEFT(UserName, 1) = @UserNameBeginsWith ORDER BY DateCreated DESC

-- Sort by username
IF @SortBy = 1 AND @SortOrder = 0
    IF @UserNameBeginsWith IS NULL
	    INSERT INTO #PageIndexForUsers (Username)
	    SELECT Username FROM Users WHERE Approved = 1 AND DisplayInMemberList = 1  ORDER BY Username
    ELSE
	    INSERT INTO #PageIndexForUsers (Username)
	    SELECT Username FROM Users WHERE Approved = 1 AND DisplayInMemberList = 1  AND LEFT(UserName, 1) = @UserNameBeginsWith ORDER BY Username
ELSE IF @SortBy = 1 AND @SortOrder = 1
    IF @UserNameBeginsWith IS NULL
	    INSERT INTO #PageIndexForUsers (Username)
	    SELECT Username FROM Users WHERE Approved = 1 AND DisplayInMemberList = 1  ORDER BY Username DESC
    ELSE
	    INSERT INTO #PageIndexForUsers (Username)
	    SELECT Username FROM Users WHERE Approved = 1 AND DisplayInMemberList = 1  AND LEFT(UserName, 1) = @UserNameBeginsWith ORDER BY Username

-- Sort by Website
IF @SortBy = 2 AND @SortOrder = 0
    IF @UserNameBeginsWith IS NULL
	    INSERT INTO #PageIndexForUsers (Username)
	    SELECT Username FROM Users WHERE Approved = 1 AND DisplayInMemberList = 1  ORDER BY Url DESC
    ELSE
	    INSERT INTO #PageIndexForUsers (Username)
	    SELECT Username FROM Users WHERE Approved = 1 AND DisplayInMemberList = 1  AND LEFT(UserName, 1) = @UserNameBeginsWith ORDER BY Url DESC
ELSE IF @SortBy = 2 AND @SortOrder = 1
    IF @UserNameBeginsWith IS NULL
	    INSERT INTO #PageIndexForUsers (Username)
	    SELECT Username FROM Users WHERE Approved = 1 AND DisplayInMemberList = 1  ORDER BY Url
    ELSE
	    INSERT INTO #PageIndexForUsers (Username)
	    SELECT Username FROM Users WHERE Approved = 1 AND DisplayInMemberList = 1  AND LEFT(UserName, 1) = @UserNameBeginsWith ORDER BY Url DESC

-- Sort by Last Active
IF @SortBy = 3 AND @SortOrder = 0
    IF @UserNameBeginsWith IS NULL
	    INSERT INTO #PageIndexForUsers (Username)
	    SELECT Username FROM Users WHERE Approved = 1 AND DisplayInMemberList = 1  ORDER BY LastActivity DESC
    ELSE
	    INSERT INTO #PageIndexForUsers (Username)
	    SELECT Username FROM Users WHERE Approved = 1 AND DisplayInMemberList = 1  AND LEFT(UserName, 1) = @UserNameBeginsWith ORDER BY LastActivity DESC
ELSE IF @SortBy = 3 AND @SortOrder = 1
    IF @UserNameBeginsWith IS NULL
	    INSERT INTO #PageIndexForUsers (Username)
	    SELECT Username FROM Users WHERE Approved = 1 AND DisplayInMemberList = 1  ORDER BY LastActivity
    ELSE
	    INSERT INTO #PageIndexForUsers (Username)
	    SELECT Username FROM Users WHERE Approved = 1 AND DisplayInMemberList = 1  AND LEFT(UserName, 1) = @UserNameBeginsWith ORDER BY LastActivity

-- Sort by TotalPosts
IF @SortBy = 4 AND @SortOrder = 1
    IF @UserNameBeginsWith IS NULL
	    INSERT INTO #PageIndexForUsers (Username)
	    SELECT Username FROM Users WHERE Approved = 1 AND DisplayInMemberList = 1  ORDER BY TotalPosts DESC
    ELSE
	    INSERT INTO #PageIndexForUsers (Username)
	    SELECT Username FROM Users WHERE Approved = 1 AND DisplayInMemberList = 1  AND LEFT(UserName, 1) = @UserNameBeginsWith ORDER BY TotalPosts DESC
ELSE IF @SortBy = 4 AND @SortOrder = 0
    IF @UserNameBeginsWith IS NULL
	    INSERT INTO #PageIndexForUsers (Username)
	    SELECT Username FROM Users WHERE Approved = 1 AND DisplayInMemberList = 1  ORDER BY TotalPosts
    ELSE
	    INSERT INTO #PageIndexForUsers (Username)
	    SELECT Username FROM Users WHERE Approved = 1 AND DisplayInMemberList = 1  AND LEFT(UserName, 1) = @UserNameBeginsWith ORDER BY TotalPosts

-- Get the user details
SELECT
	U.Username,
	Password,
	Email,
	ForumView,
	Approved,
        ProfileApproved,
	Trusted,
	FakeEmail,
	URL,
	Signature,
	DateCreated,
	TrackYourPosts,
	LastLogin,
	LastActivity,
	TimeZone,
	Location,
	Occupation,
	Interests,
	MSN,
	Yahoo,
	AIM,
	ICQ,
	TotalPosts,
	HasAvatar,
	ShowUnreadTopicsOnly,
	Style,
	AvatarType,
	ShowAvatar,
	DateFormat,
	PostViewOrder,
	FlatView,
	IsModerator = (SELECT Count(*) FROM Moderators WHERE Username = U.Username),
	AvatarUrl,
	Attributes
FROM 
	Users U (nolock),
	#PageIndexForUsers
WHERE 
	Approved = 1 AND
	U.Username = #PageIndexForUsers.Username AND
	#PageIndexForUsers.IndexID > @PageLowerBound AND
	#PageIndexForUsers.IndexID < @PageUpperBound
ORDER BY
	#PageIndexForUsers.IndexID
END









GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO