CREATE PROCEDURE dbo.[Forum_Members_GetByProfileProp]
    @PortalId INT,
    @PropertyName NVARCHAR(256),
    @PropertyValue NVARCHAR(256),
    @PageIndex INT,
    @PageSize INT
AS
    BEGIN
    -- Set the page bounds
        DECLARE @PageLowerBound INT
        DECLARE @PageUpperBound INT
        SET @PageLowerBound = @PageSize * @PageIndex
        SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    -- Create a temp table TO store the select results
        CREATE TABLE #PageIndexForUsers
            (
              IndexId INT IDENTITY(0, 1)
                          NOT NULL,
              UserId INT
            )

    -- Insert into our temp table
        INSERT  INTO #PageIndexForUsers ( UserId )
                SELECT  U.UserId
                FROM    dbo.ProfilePropertyDefinition P
                        INNER JOIN dbo.UserProfile UP ON P.PropertyDefinitionID = UP.PropertyDefinitionID
                        INNER JOIN dbo.Users U ON UP.UserID = U.UserID
                WHERE   ( PropertyName = @PropertyName )
                        AND ( PropertyValue LIKE @PropertyValue
                              OR PropertyText LIKE @PropertyValue
                            )
                        AND ( P.Portalid = @PortalId
                              OR ( P.PortalId IS NULL
                                   AND @PortalId IS NULL
                                 )
                            )
                ORDER BY U.DisplayName

        SELECT  U.[UserID],
                [PostCount],
                [EnableDisplayInMemberList],
                [EnableOnlineStatus],
                [EnablePM],
                [PortalID],
                [FirstName],
                [LastName],
                [Email],
                [Username],
                [PostCount],
                ( SELECT    COUNT(UserID)
                  FROM      dbo.Forum_vw_ForumUsers
                ) AS TotalRecords
        FROM    dbo.Forum_vw_ForumUsers U
                INNER JOIN #PageIndexForUsers P ON U.UserID = P.UserID
        WHERE   ( PortalId = @PortalId
                  OR ( PortalId IS NULL
                       AND @PortalId IS NULL
                     )
                )
                AND P.IndexId >= @PageLowerBound
                AND P.IndexId <= @PageUpperBound
        ORDER BY U.DisplayName
    END

GO