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 stackoverflow.com/questions/5487892/… — Pawan
– Pawan, Commented Jan 16, 2014 at 12:04
Thanks but that does not show the full syntax, for example where does the CASE go. — sd_dracula
– sd_dracula, Commented Jan 16, 2014 at 12:09

Gordon Linoff · Accepted Answer · 2014-01-16 12:15:29Z

2

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.

answered Jan 16, 2014 at 12:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sd_dracula Over a year ago

Thanks that did the job. The query seems to take a bit longer, would it be due to the query or because or latency?

Gordon Linoff Over a year ago

@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.

Mudassir Hasan · Accepted Answer · 2014-01-16 12:17:56Z

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

Collectives™ on Stack Overflow

sql IF in stored procedure

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related