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
@PageIndex	int	4	INPUT
@PageSize	int	4	INPUT
@UserNameToFind	nvarchar	100	INPUT

Total: 3 parameter(s)

SQL

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





CREATE       PROCEDURE forums_FindUsersByName
(
	@PageIndex int,
	@PageSize int,
	@UserNameToFind nvarchar(50)
)
AS

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)
)	

-- Insert into our temp table
INSERT INTO #PageIndexForUsers (Username)
SELECT 
	Username 
FROM 
	Users 
WHERE 
	Approved = 1 AND 
	DisplayInMemberList = 1 AND
	Username like '%' + @UserNameToFind + '%'
ORDER BY 
	DateCreated


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,
	AvatarUrl,
	ShowAvatar,
	DateFormat,
	PostViewOrder,
	IsModerator = (select count(*) from moderators where username = U.Username),
	FlatView,
	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









GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO