CREATE PROCEDURE [dbo].[Forum_Avatar_Role_GetUsers]
    (
      @PortalID INT,
      @UserID INT
    )
AS
    SELECT  R.RoleID,
            R.RoleName,
            AR.Avatar
    FROM    dbo.Roles R
            INNER JOIN dbo.UserRoles UR ON R.RoleID = UR.RoleID
            INNER JOIN dbo.Forum_Avatar_Role AR ON R.RoleID = AR.RoleID
    WHERE   R.PortalID = @PortalID
            AND UR.UserID = @UserID
            AND ( EffectiveDate < GETDATE()
                  OR EffectiveDate IS NULL
                )
            AND ( ExpiryDate > GETDATE()
                  OR ExpiryDate IS NULL
                )
    ORDER BY AR.Avatar ASC

GO