title: T-SQL function does not return all values
body: <p>I have 2 tables, <code>contact</code> and <code>membership</code></p>
  
  <p>Here is the data from my tables:</p>
  
  <p><strong>CONTACT:</strong></p>
  
  <pre><code>SERIALNUMBER    TITLE   FIRSTNAME   SURNAME
       1           Mr       John        Doe
       2           Mrs      Jane        Doe
  </code></pre>
  
  <p><strong>MEMBERSHIP:</strong></p>
  
  <pre><code>MEMBERSHIPID    NAME    SERIALNUMBER
       1          TEST        1
       1          TEST        2
  </code></pre>
  
  <p>When I write the following SELECT statement:</p>
  
  <pre><code>SELECT * 
  FROM CONTACT C
  INNER JOIN MEMBERSHIP M
  ON C.SERIALNUMBER=M.SERIALNUMBER
  WHERE M.MEMBERSHIPID = '1'
  </code></pre>
  
  <p>I see the following results:</p>
  
  <pre><code>SERIALNUMBER    TITLE   FIRSTNAME   SURNAME MEMBERSHIPID    NAME    SERIALNUMBER
       1           Mr      John        Doe         1          TEST        1
       2           Mrs     Jane        Doe         1          TEST        2
  </code></pre>
  
  <p>Then I have written the following function:</p>
  
  <pre><code>CREATE FUNCTION [dbo].[fnGetMemberNames] (@MEMBERSHIPID VARCHAR(100))
  RETURNS VARCHAR(50)
  BEGIN
    DECLARE @name_to_return AS VARCHAR(50);
    SET @name_to_return = NULL;
    SELECT  @name_to_return =
               (C.TITLE + ' ' + C.FIRSTNAME + ' ' + C.SURNAME) 
    FROM CONTACT C
    INNER JOIN MEMBERSHIP M
      ON C.SERIALNUMBER=M.SERIALNUMBER
      WHERE M.MEMBERSHIPID=@MEMBERSHIPID;
    RETURN @name_to_return ;
  
  END;
  </code></pre>
  
  <p>I call the function like this:</p>
  
  <pre><code>SELECT [dbo].[fnGetMemberNames](1)
  </code></pre>
  
  <p>But I only get one line of results.</p>
  
  <pre><code>Mrs      Jane        Doe
  </code></pre>
  
  <p>I should be seeing a carriage return OR comma delimited list with <code>Mr John Doe</code></p>