title: Function and procedure with cursor (SQL Server)
body: <p>Sorry for asking these question here. I've found online and in book, but didn't got something similar.</p>
  
  <ol>
  <li>Create the function float <code>sf_getAverageGrade(@sname nchar(10))</code>.</li>
  </ol>
  
  <p>Requirements:</p>
  
  <ul>
  <li>The function returns -1 if there is no student called <code>@sname</code>; </li>
  <li>returns -2 if there are more than one student called <code>@sname</code>; </li>
  <li>returns the average grade of student sname if he/she has at least one course grade;</li>
  <li>returns null if student <code>@sname</code> has no course grade.</li>
  </ul>
  
  <p>I tried to write it like this:(<strong>my question is it seems logical but it doesn't work. Please help me figure out why. Thanks</strong>)</p>
  
  <pre><code>CREATE FUNCTION sf_getAverageGrade
      (@sname NCHAR(10))
  RETURNS FLOAT
  AS
  BEGIN
      DECLARE @n NCHAR(10), @countsname INTEGER, @countgrade INTEGER, @avggrade FLOAT 
      SET @n = (SELECT sname FROM student)
  
      SET @countsname = (SELECT COUNT(sno) FROM student WHERE sname = @sname)
  
      SET @countgrade = (SELECT COUNT(grade) 
                         FROM sc 
                         GROUP BY sno 
                         HAVING sno = (SELECT sno FROM student WHERE sname = @sname))
  
      SET @avggrade = (SELECT AVG(grade) FROM sc 
                       GROUP BY sno 
                       HAVING sno = (SELECT sno FROM student WHERE sname = @sname))
  
      IF @n &lt;&gt; @sname
          RETURN -1
      ELSE IF @countsname &gt; 1
          RETURN -2
      ELSE IF @countgrade &gt;= 1
          RETURN @avggrade
      ELSE IF @countgrade = 0
          RETURN 'null'
  
      RETURN 0
  END
  </code></pre>
  
  <p>2.Create a stored procedure <code>sp_displayGradeInRow(@sname nchar(10))</code></p>
  
  <p>The stored procedure displays information of two rows: </p>
  
  <ul>
  <li>the first row includes ‘name’, all course names selected by @sname and ‘average grade’</li>
  <li>the second row includes @sname, the grades and average grade. (Using cursor to get course names and grades) </li>
  </ul>
  
  <p>Show the stored procedure result when the parameter @sname equals ‘Jack’ </p>
  
  <p><strong>For this one, I just don't know how to let these information display in two rows.</strong></p>
  
  <p>Thank for your help. I just need some clue.</p>