Conditional AND in select statement SQL Server

Question

I have a select statement in a stored procedure and @date is the parameter for the stored procedure.

This is my code:

SELECT * 
FROM vw_A a               
INNER JOIN vw_B b ON a.id = b.id              
WHERE 
    a.imdt IS NULL     
    AND a.appflg = 'Y'                
    AND a.last_dt > cast(@date as datetime);    

Now the table vw_A also has a Date column prgDate.

This column could be null, or it will contain a datetime value.

I want to change the above select query in such a way that

• if prgDate is null, then the above select statement uses the condition (AND a.last_dt > cast(@date as datetime))
• else the select statement should use prgDate value instead of last_dt in the condition: (AND a.prgDate > cast(@date as datetime))

How could I achieve this in the select statement?

Answer 1

Try this:

SELECT * 
FROM vw_A a               
INNER JOIN vw_B b ON a.id = b.id              
WHERE 
    a.imdt IS NULL     
    AND a.appflg = 'Y'                
    AND ISNULL(a.prgDate, a.last_dt) > cast(@date as datetime);