CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Lukas Jankauskas
Lukas Jankauskas

Reputation: 31

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.

Upvotes: 0

Views: 70

Answers (2)

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56697

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.

Upvotes: 4

Ash
Ash

Reputation: 6035

Assuming you're using sql-server, try using iif instead

INSERT INTO @trAll
SELECT tr.EventDateTime
,tr.OrderId
,'EX'
,round(tr.Prod01_CompB_QuantityPV_LTR + tr.Prod01_Fuel_QuantityPV_LTR+ iif(tr.Prod01_AdditiveA>0, tr.Prod01_AdditiveA, 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'

Upvotes: 3

Related Questions