title: Execute Treatements Before Returnig Table with a Table Valued Function?
body: <p>I have a Table Valued Function , that use to return a Table ,result of an inner join :</p>
  
  <pre><code>ALTER FUNCTION  [dbo].[FT_GET_ListePreinscriptionUsagerActivite]
   (
       @UsagerID int
    )
    RETURNS TABLE 
    AS
    RETURN 
   (
    select TbReunion.Ident_Reunion, TbReunion.Intitule_Reunion,TbReunion.Date_Reunion,TbUsager.Nom_Usager,TbUsager.Prenom_Usager ,TbReunion.Heure_Deb_Rn,TbReunion.Heure_Fin_Rn , TbIntervenants.Nom_Employé  from TbReunion Inner Join TbParticipants on TbReunion.Ident_Reunion=TbParticipants.Ident_Reunion
                          Inner Join TbUsager on TbUsager.IdUsager = TbParticipants.Ident_Participant
                          Inner Join TbIntervenant_Formation on TbReunion.Ident_Reunion=TbIntervenant_Formation.Ident_Reunion
                          Inner Join TbIntervenants on TbIntervenants.Num_Employe=TbIntervenant_Formation.Ident_Intervenant
   where TbParticipants.Type_Participant=1 And  TbParticipants.Indic_Maj ='P' And TbUsager.IdUsager=@UsagerID and TbIntervenant_Formation.IdRole_Intervenant=42
    )
  </code></pre>
  
  <p>this function returns to me results as below :</p>
  
  <pre><code>id c2 
   1  a 
   1  b
   2  c
  </code></pre>
  
  <p>what i want is to get this type of result :</p>
  
  <pre><code>id c2
   1  a , b
   2  c
  </code></pre>
  
  <p>so  i need to commit some changes to the function as  this :</p>
  
  <ul>
  <li><p>store the result of the inner join into a table</p>
  
  <pre><code> select * into newTable from (
   select TbReunion.Ident_Reunion, TbReunion.Intitule_Reunion,TbReunion.Date_Reunion,TbUsager.Nom_Usager,TbUsager.Prenom_Usager ,TbReunion.Heure_Deb_Rn,TbReunion.Heure_Fin_Rn , TbIntervenants.Nom_Employé  from TbReunion Inner Join TbParticipants on TbReunion.Ident_Reunion=TbParticipants.Ident_Reunion
                      Inner Join TbUsager on TbUsager.IdUsager = TbParticipants.Ident_Participant
                      Inner Join TbIntervenant_Formation on TbReunion.Ident_Reunion=TbIntervenant_Formation.Ident_Reunion
                      Inner Join TbIntervenants on TbIntervenants.Num_Employe=TbIntervenant_Formation.Ident_Intervenant
   where TbParticipants.Type_Participant=1 And  TbParticipants.Indic_Maj ='P' And TbUsager.IdUsager=@UsagerID and TbIntervenant_Formation.IdRole_Intervenant=42)
  </code></pre></li>
  <li><p>concatenate the  rows that have the same id to get the expeceted result </p>
  
  <pre><code> select Ident_Reunion,Intitule_Reunion,Date_Reunion,Nom_Usager ,Prenom_Usager, Heure_Deb_Rn,Heure_Fin_Rn,
   Nom_Employé=Stuff(
   (   
    SELECT  ', ' + Nom_Employé
     FROM newTable b 
     WHERE b.Ident_Reunion = a.Ident_Reunion 
    FOR XML PATH('')      
   )
    , 1, 2, '') from newTable a
   group  by Ident_Reunion,Intitule_Reunion,Date_Reunion,Nom_Usager ,Prenom_Usager, Heure_Deb_Rn,Heure_Fin_Rn
  </code></pre></li>
  </ul>
  
  <p>however when i do that i get errors :
     for </p>
  
  <blockquote>
    <p>select * into newTable from (
    i get  <strong><em>"invalid use of an operator with side effects &lt;> in a function"</em></strong></p>
  </blockquote>
  
  <p>and  then for </p>
  
  <blockquote>
    <p>select Ident_Reunion,Intitule_Reunion,Date_Reunion ....
    i get <strong><em>"selection statements included in a function cannot return data to a client"</em></strong></p>
  </blockquote>
  
  <p>, so  i searched how i can solve that and  i found a type of table valued function called "Multi-statement table-valued functions " but even that function returns errors when i use it ?</p>
  
  <p>so any idea on how to make some treatements before i return  results with table valued function ?</p>