sql IF in stored procedure

Question

I have a stored procedure that pulls data from 2 different on 2 different servers as follows:

SELECT 
        LocalDB.Record_ID,  
        LocalDB.Total/1440 as TotalTime,
        ((REMOTE_ACTIVE.Time_Adjusted) - (REMOTE_ACTIVETimes.CALCTimeSec)) as TimeLeft,
        LocalDB.isManualEntry
        FROM LocalDatabase.dbo.Records LocalDB left join
                   [RemoteServer].[Reporting].[dbo].[ActiveWO] REMOTE_ACTIVE ON
                   LocalDB.WO_ID = REMOTE_ACTIVE.[PO_ID]
                   left join [RemoteServer].[Reporting].[dbo].[ActiveWOTimes] REMOTE_ACTIVETimes ON
            LocalDB.WO_ID = REMOTE_ACTIVETimes.[PO_ID]

What can happen is that sometimes the "TimeLeft" value can be 0. When that happens I want to replace that value with something like

IF(TimeLeft is 0 or null)
(getdate() - LocalDB.CreatedDate) as TimeElapsed

The thing is, I'm not sure how to implement such an IF statement or if it is even possible.

you can use case statement in sql serve r. Please refer http://stackoverflow.com/questions/5487892/sql-server-case-when-or-then-else-end-the-or-is-not-supported — Pawan, Jan 16 '14 at 12:04
Thanks but that does not show the full syntax, for example where does the CASE go. — sd_dracula, Jan 16 '14 at 12:09

score 2 · Accepted Answer · answered Jan 16 '14 at 12:15

Just add a case statement:

SELECT LocalDB.Record_ID,  
       LocalDB.Total/1440 as TotalTime,
       (case when REMOTE_ACTIVE.Time_Adjusted - REMOTE_ACTIVETimes.CALCTimeSec <> 0
             then REMOTE_ACTIVE.Time_Adjusted - REMOTE_ACTIVETimes.CALCTimeSec
             else getdate() - LocalDB.CreatedDate
        end) as TimeLeft,
       LocalDB.isManualEntry
FROM LocalDatabase.dbo.Records LocalDB left join
     [RemoteServer].[Reporting].[dbo].[ActiveWO] REMOTE_ACTIVE
     ON LocalDB.WO_ID = REMOTE_ACTIVE.[PO_ID] left join
     [RemoteServer].[Reporting].[dbo].[ActiveWOTimes] REMOTE_ACTIVETimes
     ON LocalDB.WO_ID = REMOTE_ACTIVETimes.[PO_ID];

I assume in the question that TimeElapsed is the replacement for TimeLeft.

Thanks that did the job. The query seems to take a bit longer, would it be due to the query or because or latency? — sd_dracula, Jan 16 '14 at 12:53
@sd_dracula . . . I seriously doubt that adding this `case` statement would have much impact on performance. I suspect that is just natural variation, based on what is running on the server and how what data is in the various page caches. — Gordon Linoff, Jan 16 '14 at 12:55

Mudassir Hasan · Answer 2 · 2014-01-16T12:17:56.740

You can use a Subquery

Select RecordID,TotalTime,TimeLeft,IsManualEntry,
       CASE WHEN TimeLeft=0 OR TimeLeft is null THEN DATEDIFF(day, GETDATE(), CreationDate)
            ELSE  CreationDate END AS TimeElapsed,

FROM (
        SELECT  LocalDB.CreatedDate As CreationDate
        LocalDB.Record_ID as RecordID,  
        LocalDB.Total/1440 as TotalTime,
        ((REMOTE_ACTIVE.Time_Adjusted) - (REMOTE_ACTIVETimes.CALCTimeSec)) as TimeLeft,
        LocalDB.isManualEntry as IsManualEntry
        FROM LocalDatabase.dbo.Records LocalDB left join
                   [RemoteServer].[Reporting].[dbo].[ActiveWO] REMOTE_ACTIVE ON
                   LocalDB.WO_ID = REMOTE_ACTIVE.[PO_ID]
                   left join [RemoteServer].[Reporting].[dbo].[ActiveWOTimes]  
                   REMOTE_ACTIVETimes ON
                   LocalDB.WO_ID = REMOTE_ACTIVETimes.[PO_ID]
      ) Z

More about DATEDIFF

sql IF in stored procedure

2 Answers2