fnUserRoles

CREATE FUNCTION [dbo].[fnUserRoles]
    (
    @ClientUsersID int,
    @ClientsID int
    )
RETURNS TABLE 
AS
RETURN

    select distinct ALLROLES.ID ClientRolesID, @ClientsID ClientsID
    from
    (SELECT TOP 1 ClientRoles.ID ClientRolesID
    FROM ClientRoles 
    where ClientsID=@ClientsID AND EveryoneRole=1 AND Deleted=0
    UNION ALL
    SELECT UserRoles.ClientRolesID
    FROM UserRoles 
    JOIN ClientRoles ON UserRoles.ClientRolesID=ClientRoles.ID
    WHERE UserRoles.ClientUsersID=@ClientUsersID AND @ClientUsersID > 0
    AND ClientRoles.EveryOneRole=0 
    AND ClientRoles.Deleted=0) EXPLICITROLES
    cross apply dbo.UFN_CLIENTROLE_GETPARENTIDS(EXPLICITROLES.ClientRolesID) ALLROLES