Correct Use of IF ELSE Clause in SQL Server 2012

Question

I am new to SQL and want to execute a statement if condition is true, and another statement in case of false.

SQL Server 2012 Query

select

   if s.SpecialInsttPlan = 'No' 

   BEGIN
        (s.TotalBill - s.Advance) / s.Installments as Installment 
   else
         'Special' as Installment 
   END
from
   SalesInvoice s 
   left join
      InstallmentPaymentHistory iph 
      on iph.SalesInvoiceID = s.SalesInvoiceID 
where
   iph.SalesInvoiceID = 41 
group by
   s.TotalBill,
   s.Advance,
   s.Installments

My query ends with following errors

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'if'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 's'.

Please help to resolve it

Possible duplicate of [How do I perform an IF...THEN in an SQL SELECT?](https://stackoverflow.com/questions/63447/how-do-i-perform-an-if-then-in-an-sql-select) — Peter B, Apr 09 '19 at 12:14

Sreenu131 · Accepted Answer · 2019-04-09T12:21:12.580

1

Use Case statement instead of If.There is a difference between IF and CASE statement in SQL If statements are used to run sql steps in a batch and a case statement determines which value to use in a column of a select statement

 SELECT
    CASE WHEN s.SpecialInsttPlan = 'No' 
        THEN(s.TotalBill - s.Advance) / s.Installments 
        ELSE 'Special' 
    END As Installment 
FROM SalesInvoice s 
LEFT JOIN InstallmentPaymentHistory iph 
    ON iph.SalesInvoiceID = s.SalesInvoiceID 
WHERE iph.SalesInvoiceID = 41 
GROUP BY
    s.TotalBill,
    s.Advance,
    s.Installments

edited Apr 09 '19 at 12:21

answered Apr 09 '19 at 12:14

Sreenu131

2,476
1
7
18

1

You did the hard stuff, now add a line or two to explain the difference between case and if. – George Menoutis Apr 09 '19 at 12:15
1

`CASE` is an expression. The difference between expression and statement is subtle, but important, especially in this scenario. See [this post](https://sqlperformance.com/2014/06/t-sql-queries/dirty-secrets-of-the-case-expression). – Aaron Bertrand Apr 09 '19 at 12:29

score 0 · Answer 2 · answered Apr 09 '19 at 12:37

First of all it's not required to use if here. you have dual condition in selection of column then it's preferred to use CASE instead of IF. also you have used LEFT JOIN and kept where condition for iph.SalesInvoiceID it should be inside on if it's left join else left and where makes INNER JOIN in your case.

SELECT 
   CASE WHEN s.SpecialInsttPlan = 'No'  THEN  (s.TotalBill - s.Advance) / s.Installments ELSE 'Special' AS Installment
   FROM SalesInvoice s
   LEFT JOIN  InstallmentPaymentHistory iph ON iph.SalesInvoiceID = s.SalesInvoiceID AND iph.SalesInvoiceID = 41 
   group by
   s.TotalBill,
   s.Advance,
   s.Installments

Correct Use of IF ELSE Clause in SQL Server 2012

2 Answers2