CREATE PROCEDURE dbo.[Forum_ManageUsers_GetUsersByRolename]
    @PortalId INT,
    @Rolename NVARCHAR(50),
    @PageIndex INT,
    @PageSize INT
AS
    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.UserPortals AS UP
                    RIGHT OUTER JOIN dbo.UserRoles UR
                    INNER JOIN dbo.Roles R ON UR.RoleID = R.RoleID
                    RIGHT OUTER JOIN dbo.Users AS U ON UR.UserID = U.UserID ON UP.UserId = U.UserID
            WHERE   ( UP.PortalId = @PortalId
                      OR @PortalId IS NULL
                    )
                    AND ( R.RoleName = @Rolename )
                    AND ( R.PortalId = @PortalId
                          OR @PortalId IS NULL
                        )
            ORDER BY U.Username

    SELECT  *
    FROM    dbo.vw_Users u,
            #PageIndexForUsers p
    WHERE   u.UserId = p.UserId
            AND ( PortalId = @PortalId
                  OR ( PortalId IS NULL
                       AND @PortalId IS NULL
                     )
                )
            AND p.IndexId >= @PageLowerBound
            AND p.IndexId <= @PageUpperBound
    ORDER BY u.UserName

    SELECT  TotalRecords = COUNT(*)
    FROM    #PageIndexForUsers

GO