where clause question

Question

Someone asked me whether you could use an 'if' statement in a 'where' clause.    I'm not wanting to get into the merits of whether you should do this or not (I would use a 'case' statement myself), but i was wondering if it was possible, and if so, can you provide a basic example.    Thanks

Answer 1

Hello,

A T-SQL a IF THEN/ELSE is a program flow command, you can't use it in a WHERE clause; you could only use a CASE WHEN statement.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

Thanks Olaf.   Thats kind of what I thought, but I wanted to get a second opinion.

Answer 3

Hi

Good question I need to add it to my SQL Where clause tutorial.

Below is an example:

SELECT *
  FROM TableName
  where (case when datepart(WEEKDAY,getdate()) IN (7,1) then 1 else 0 end) = 1

IF (using case) is handy for complex queries. I don't often use it using sql where but I do often use it with aggregate functions.

Regards

Emil