title: Using CASE function in SQL
body: <p>I have a table like:</p>
  
  <pre><code>2   Savita  EC1 50
  2   Savita  EC2 55
  2   Savita  EC3 28
  2   Savita  EC4 30
  2   Savita  EC5 60
  3   Abhi    EC1 40
  3   Abhi    EC2 38
  3   Abhi    EC3 35
  3   Abhi    EC4 45
  3   Abhi    EC5 37
  4   Priya   EC1 60
  4   Priya   EC2 49
  4   Priya   EC3 26
  4   Priya   EC4 29
  4   Priya   EC5 44
  5   Shanthi EC1 70
  5   Shanthi EC2 19
  5   Shanthi EC3 45
  5   Shanthi EC4 44
  5   Shanthi EC5 50
  6   Harish  EC1 60
  6   Harish  EC2 64
  6   Harish  EC3 26
  6   Harish  EC4 28
  6   Harish  EC5 29
  </code></pre>
  
  <p>I want to add grace marks and the condition is
  total grace marks per student is max of 6 marks, if the candidate has failed in two subs like 28 in EC1 and 27 in EC2, then after adding grace EC1=30 and EC2=30. and he is pass. if he has 25 marks in EC1 and 28 marks in EC2 , then  grace mark needed is 5+2=7 . hence he is fail and no grace marks added, Grace marks can be added to max two subjects. if he failed in more than 2 subjects then he is failed no grace marks added.
  I have a proc Like</p>
  
  <pre><code>create procedure SP_student3
  as
  begin
  select FstudentName,EC1,EC2,EC3,EC4,EC5,TOTALMARKS, CASE 
  WHEN EC1&lt;30 THEN 'FAIL'
  WHEN EC2&lt;30 THEN 'FAIL'
  WHEN EC3&lt;30 THEN 'FAIL'
  WHEN EC4&lt;30 THEN 'FAIL'
  WHEN EC5&lt;30 THEN 'FAIL'
  ELSE 'PASS' END AS RESULT FROM(select FstudentName,EC1,EC2,EC3,EC4,EC5,TOTALMARKS=EC1+EC2+EC3+EC4+EC5 FROM Student   PIVOT(SUM(FMarks) for Fsubject in ([EC1],[EC2],[EC3],[EC4],[EC5],TOTALMARKS]))ASPIVOTTABLE )B end
  </code></pre>
  
  <p>Which gives out put as</p>
  
  <pre><code>Abhi    40  38  35  45  37  195 PASS
  Harish  60  64  26  28  29  207 FAIL
  Priya   60  49  26  29  44  208 FAIL
  Savita  50  55  28  30  60  223 FAIL
  Shanthi 70  19  45  44  50  228 FAIL
  </code></pre>