Create function [dbo].[yaf_user_getgroupnames]
(
@UserID INT
) returns VARCHAR(MAX)
BEGIN
	DECLARE @Output VARCHAR(MAX)
		SELECT @Output = COALESCE(@Output+', ', '') + f.Name from [dbo].[yaf_Group] f 
		inner join [dbo].[yaf_UserGroup] e on f.GroupID=e.GroupID and e.UserID = @UserID
	RETURN @Output
END

ALTER procedure [dbo].[yaf_user_list](@BoardID int,@UserID int=null,@Approved bit=null,@GroupID int=null,@RankID int=null,@StyledNicks bit = null) as
begin	
	if @UserID is not null
		 select 
			a.*,
			a.NumPosts,
			CultureUser = a.Culture,			
			b.RankID,						
			RankName = b.Name,
			Style = case(@StyledNicks)
			when 1 then  ISNULL(( SELECT TOP 1 f.Style FROM [dbo].[yaf_UserGroup] e 
			join [dbo].[yaf_Group] f on f.GroupID=e.GroupID WHERE e.UserID=a.UserID AND LEN(f.Style) > 2 ORDER BY f.SortOrder), b.Style)  
			else ''	 end, 
			NumDays = datediff(d,a.Joined,GETUTCDATE() )+1,
			NumPostsForum = (select count(1) from [dbo].[yaf_Message] x where (x.Flags & 24)=16),
			HasAvatarImage = (select count(1) from [dbo].[yaf_User] x where x.UserID=a.UserID and AvatarImage is not null),
			IsAdmin	= IsNull(c.IsAdmin,0),
			IsGuest	= IsNull(a.Flags & 4,0),
			IsHostAdmin	= IsNull(a.Flags & 1,0),
			IsForumModerator	= IsNull(c.IsForumModerator,0),
			IsModerator		= IsNull(c.IsModerator,0)
		from 
			[dbo].[yaf_User] a
			join [dbo].[yaf_Rank] b on b.RankID=a.RankID			
			left join [dbo].[yaf_vaccess] c on c.UserID=a.UserID
		where 
			a.UserID = @UserID and
			a.BoardID = @BoardID and
			IsNull(c.ForumID,0) = 0 and
			(@Approved is null or (@Approved=0 and (a.Flags & 2)=0) or (@Approved=1 and (a.Flags & 2)=2))
		order by 
			a.Name 
	else if @GroupID is null and @RankID is null
		select 
			a.*,
			a.NumPosts,
			CultureUser = a.Culture,	
			Style = case(@StyledNicks)
			when 1 then  ISNULL(( SELECT TOP 1 f.Style FROM [dbo].[yaf_UserGroup] e 
			join [dbo].[yaf_Group] f on f.GroupID=e.GroupID WHERE e.UserID=a.UserID AND LEN(f.Style) > 2 ORDER BY f.SortOrder), b.Style)  
			else ''	 end, 	
			IsAdmin = (select count(1) from [dbo].[yaf_UserGroup] x join [dbo].[yaf_Group] y on y.GroupID=x.GroupID where x.UserID=a.UserID and (y.Flags & 1)<>0),
			IsGuest	= IsNull(a.Flags & 4,0),
			IsHostAdmin	= IsNull(a.Flags & 1,0),
			b.RankID,
			RankName = b.Name,
			(dbo.yaf_user_getgroupnames (a.UserID)) as GroupNames
		from 
			[dbo].[yaf_User] a
			join [dbo].[yaf_Rank] b on b.RankID=a.RankID			
		where 
			a.BoardID = @BoardID and
			(@Approved is null or (@Approved=0 and (a.Flags & 2)=0) or (@Approved=1 and (a.Flags & 2)=2))
		order by 
			a.Name
	else
		select 
			a.*,
			a.NumPosts,
			CultureUser = a.Culture,
			IsAdmin = (select count(1) from [dbo].[yaf_UserGroup] x join [dbo].[yaf_Group] y on y.GroupID=x.GroupID where x.UserID=a.UserID and (y.Flags & 1)<>0),
			IsGuest	= IsNull(a.Flags & 4,0),
			IsHostAdmin	= IsNull(a.Flags & 1,0),
			b.RankID,
			RankName = b.Name,
			Style = case(@StyledNicks)
			when 1 then  ISNULL(( SELECT TOP 1 f.Style FROM [dbo].[yaf_UserGroup] e 
			join [dbo].[yaf_Group] f on f.GroupID=e.GroupID WHERE e.UserID=a.UserID AND LEN(f.Style) > 2 ORDER BY f.SortOrder), b.Style)  
			else ''	 end 
		from 
			[dbo].[yaf_User] a
			join [dbo].[yaf_Rank] b on b.RankID=a.RankID			
		where 
			a.BoardID = @BoardID and
			(@Approved is null or (@Approved=0 and (a.Flags & 2)=0) or (@Approved=1 and (a.Flags & 2)=2)) and
			(@GroupID is null or exists(select 1 from [dbo].[yaf_UserGroup] x where x.UserID=a.UserID and x.GroupID=@GroupID)) and
			(@RankID is null or a.RankID=@RankID)
		order by 
			a.Name
end