>I have a table TeamWork, which is the master table. Then, I have TeamWorkMember which contains zero, one or more members associated to TeamWork. So, TeamWork.Numero=TeamWorkMember.NoTeamWork.
>
>The goal is to list all TeamWork records where it has a specific TeamWorkMember record in it based on a member.
>
>For example, I have this SQL:
>
>

>SELECT TeamWork.Numero,TeamWorkMember.NoMember
>FROM TeamWork
>OUTER APPLY (SELECT TOP 1 TeamWorkMember.NoMember FROM TeamWorkMember WHERE TeamWork.Numero=TeamWorkMember.NoTeamWork) TeamWorkMember
>WHERE TeamWorkMember.NoMember=958 
>

>
>This is good. It gives me all TeamWork records having a child record with member 958. But, this is only valid if this is the first member assigned to the task. So, if for a TeamWork record, I have two child records, such as 421 and 958, it will not find it. This is because I have a TOP 1 in the OUTER APPLY and that will take only those ending on top of that INNER SQL.
>
>If I do not include the TOP 1, it will work but it will not work if I have no WHERE clause because it will return multiple occurences of the master table based on the number of child records related to it.
>
>This SQL has to be generic and work with or without the WHERE clause. So, if no WHERE clause is in there, I need to have all master records. Thus, if I have 911 records, I need to have 911 records. But, if there is a WHERE clause, it needs to give me only those where I have the member in the related records. So, if member 958 is within 12 records of the master table, than only those 12 records need to be selected.
>
>Again, the SQL has to be generic with or without the where clause. Is there a way to achieve that?

So, what do you pass for all master rows? Suppose 0 means give me all Master records, your SQL will be

select TW.Numero from Teamwork TW where @Param = 0 OR exists (select 1 from TeamWorkMember TWM where TWM.NoTeamWork = TW.Numero and TWM.NoMember = @Param) OPTION (recompile)

OPTION (RECOMPILE) is important and will produce good plan in case you're using SQL 2008 R2 and up.

Otherwise you may want to create dynamic SQL.