Lukas Jankauskas Lukas Jankauskas - 5 months ago 25
SQL Question

SQL Stored procedures if statement inside select

sorry if this will be a dumb question, it's first time i'm working with SQL stored procedures. The question is simple - how can I declare if statement inside SELECT? To show what I mean, here is a code I currently have:


INSERT INTO @trAll
SELECT tr.EventDateTime
,tr.OrderId
,'EX'
,round(tr.Prod01_CompB_QuantityPV_LTR + tr.Prod01_Fuel_QuantityPV_LTR,0)
,tr.eAD
,tr.OperatorName
--
,cmr.ConsigneeName
,cmr.ConsigneeCompanyCode
,cmr.SenderName
,cmr.SenderCompanyCode
FROM vwTransaction AS tr
inner join tbOrderDetailCMR as cmr
on tr.orderid = cmr.OrderId
WHERE tr.EventDateTime BETWEEN @From
AND @to
AND ProductName = 'BIODIESELZ'


And this is what i need (hope you will understand what I mean):

INSERT INTO @trAll
SELECT tr.EventDateTime
,tr.OrderId
,'EX'
,round(tr.Prod01_CompB_QuantityPV_LTR + tr.Prod01_Fuel_QuantityPV_LTR+ (if (tr.Prod01_AdditiveA>0) THEN tr.Prod01_AdditiveA ELSE tr.Prod01_AdditiveB),0)
,tr.eAD
,tr.OperatorName
--
,cmr.ConsigneeName
,cmr.ConsigneeCompanyCode
,cmr.SenderName
,cmr.SenderCompanyCode
FROM vwTransaction AS tr
inner join tbOrderDetailCMR as cmr
on tr.orderid = cmr.OrderId
WHERE tr.EventDateTime BETWEEN @From
AND @to
AND ProductName = 'BIODIESELZ'


In short what I need is to add AdditiveA if its value is bigger than 0, else AdditiveB inside select statement.

Answer

By the syntax you're using I assume that this is T-SQL (Microsoft SQL-Server):

You can not use IF inside a statement like that. Use CASE here, like this:

...
,round(tr.Prod01_CompB_QuantityPV_LTR + tr.Prod01_Fuel_QuantityPV_LTR+ 
 (CASE 
     WHEN (tr.Prod01_AdditiveA>0) THEN tr.Prod01_AdditiveA 
     ELSE tr.Prod01_AdditiveB
 END),0) 
...

EDIT: In fact, now that I read it, Ashwin Nairs answer is even better. He uses the IIF command, which is less "intrusive". You might want to look at and/or accept his answer.