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
@UsernameToSearchFor	nvarchar	100	INPUT
@Username	nvarchar	100	INPUT

Total: 4 parameter(s)

SQL

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE procedure Search_ForUser (
	@PageIndex int,
	@PageSize int,
	@UsernameToSearchFor nvarchar(50),
	@Username nvarchar(50)
)
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 #PageIndexForSearchResults
(
	IndexId int IDENTITY (1, 1) NOT NULL,
	PostID int
)	

-- SELECT into the table
IF @Username IS NULL
BEGIN
	INSERT INTO #PageIndexForSearchResults (PostID)
	SELECT 
		PostID 
	FROM 
		Posts 
	WHERE
		Username = @UsernameToSearchFor AND
		ForumID NOT IN (SELECT ForumID from PrivateForums)
	ORDER BY
		PostDate DESC
END
ELSE
BEGIN

	INSERT INTO #PageIndexForSearchResults (PostID)
	SELECT 
		PostID 
	FROM 
		Posts 
	WHERE
		Username = @UsernameToSearchFor AND
		(ForumID NOT IN (SELECT ForumID from PrivateForums) OR
		ForumID IN (SELECT ForumID FROM PrivateForums WHERE RoleName IN (SELECT RoleName from UsersInRoles WHERE username = @Username)))
	ORDER BY
		PostDate DESC
END


-- Ok, our temp table has the results now return on the appropriate values
SELECT
	#PageIndexForSearchResults.PostID,
	ParentID,
	ThreadID,
	PostLevel,
	SortOrder,
	UserName,
	Subject,
	PostDate,
	ThreadDate,
	Approved,
	ForumID,
	Replies = (SELECT COUNT(*) FROM Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
	Body,
	TotalViews,
	IsLocked,
	HasRead = 0 -- not used
FROM
	Posts P,
	#PageIndexForSearchResults
WHERE
	P.PostID = #PageIndexForSearchResults.PostID AND
	#PageIndexForSearchResults.IndexID > @PageLowerBound AND
	#PageIndexForSearchResults.IndexID < @PageUpperBound
ORDER BY
	#PageIndexForSearchResults.IndexId
END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO