Use IF in SQL Server

Question

I have a small query in my stored procedure:

case when t.status = 4 and (select top 1 nd.status from tableND 
where nd.Code= t.Code order by nd.ID desc)=4 
then 6 else t.status end) as status from tableTC t

The problem is the speed performance too slow. So, I want to change it to another query.

What should I do now ?

show some sample data, it will give a better understanding of what the query is doing and possibly the execution plan. Do you have an index on t.code? — dbajtr, Sep 08 '17 at 09:49
Possible duplicate of https://stackoverflow.com/questions/63447/how-do-i-perform-an-if-then-in-an-sql-select?rq=1 — Víctor López, Sep 08 '17 at 10:32

score 0 · Answer 1 · answered Sep 08 '17 at 09:56

I couldn't do any test (you didn't post tables structures and sample data).

To avoid the SELECT inside CASE (repeated every time) you can try something like this:

CASE WHEN T.status=4 AND ND.STATUS=4 THEN 6 ELSE T.STATUS END AS STATUS
FROM tableTC T
LEFT JOIN (SELECT CODE, STATUS, ROW_NUMBER() OVER (PARTITION BY CODE ORDER BY ID DESC) AS RN FROM TABLED) ND ON T.CODE=ND.CODE AND ND.RN=1

Use IF in SQL Server

1 Answers1