CODEWITHSUNDEEP
sql-server
Qudsia
Qudsia

Reputation: 5

Correct Use of IF ELSE Clause in SQL Server 2012

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

Upvotes: 0

Views: 73

Answers (2)

Ketan Kotak
Ketan Kotak

Reputation: 957

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

Upvotes: 0

Sreenu131
Sreenu131

Reputation: 2516

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

Upvotes: 1

Related Questions