title: My first table valued function and cursor
body: <p>I have this query: </p>
  
  <pre><code>SELECT name, lastname
  FROM contestant 
  WHERE  name= 'John'  AND lastname = 'Smith'
  </code></pre>
  
  <p>I get several results from the query above and I need to use them for the following query: </p>
  
  <pre><code>SELECT  name, lastname,prize, city 
  FROM draw
  WHERE  name= name from table contestant  AND lastname= name from table contestant  
  </code></pre>
  
  <p>Now I’m building a table valued function with a cursor and a WHILE so I can have a table with the results.</p>
  
  <p>Here’s my try, can you please help me complete it? it will be very helpful to me in order to understand this TSQL chapter.  Thanks! </p>
  
  <pre><code>CREATE FUNCTION [dbo].[myFunction]
  (
      @name varchar (44),
      @lastname varchar (44) 
  )
  RETURNS 
  @tmpTable TABLE 
  (   
      name char(44),
      lastname char(44),
      prize varchar(44),
      city char(44)
  
  )
  AS
  BEGIN
  
      DECLARE 
  /* what do I have to input here */
  
      DECLARE myCursor CURSOR FOR
  
  SELECT name, lastname
  FROM contestant 
  WHERE  name= @name  AND lastname = @lastname
  
      OPEN myCursor
  
      FETCH NEXT FROM myCursor INTO  /* what goes here?*/
  
  
  
      WHILE (@@FETCH_STATUS = 0) 
      BEGIN
  
   -- and here? 
  
          FETCH NEXT FROM myCursor INTO /* what goes here?*/
  
  
      END /*WHILE*/
  
      CLOSE myCursor
      DEALLOCATE myCursor
  
  
      INSERT INTO @tmpTable (name, lastname,prize, city)
      SELECT name, lastname,prize, city 
          FROM prize
          WHERE name = @name AND lastname = @lastname
  
      RETURN
  END
  </code></pre>