Is this conditional WHERE clause possible?

Question

Let's say at the beginning of a stored procedure I do this:  DECLARE @hasActualHours BIT

I'll then employ logic to set this local variable to either 0 or 1.

I'm interested to know if I can then use it to either include or exclude entire "AND..." clauses within my WHERE clause?

For example:

SELECT ...
FROM ...
WHERE
  Clause 1... AND
  Clause 2... AND

Now, I have a Clause 3 but I only want to include it if @hasActualHours = 1.

Is it possible to do something like that in T-SQL?

Robert

Answer 1

I think simple IF ELSE or nested statement will allow us to include Clause3 or expressions. Removing/Including WHERE clause will impact the return result set but we can use IF statement to SELECT multiple option. For instance,

IF @hasActualHours = 1 
	SELECT ...
	 FROM ...
	 WHERE
	   Clause 1... AND
	   Clause 2... AND
	   Clause/Expression 3
ELSE
	SELECT ...
	 FROM ...
	 WHERE
	   Clause 1... AND
	   Clause 2... AND

---

Regards, RSingh

Answer 2

SELECT ...
FROM ...
WHERE
  Clause 1... AND
  Clause 2... AND
  case when @hasActualHours = 1 and Clause 3 then 1 else 0 end = 1

---

Saeid Hasani [sqldevelop]

Answer 3

Another option

SELECT ...
FROM ...
WHERE
  Clause 1... AND
  Clause 2... AND
  (not @hasActualHours = 1 or Clause 3)
-- If @hasActualHours not 1 then the last condition is true, therefore it is like we did not use it
-- If @hasActualHours = 1 then you have to get true on the "Clause 3" condition

---

[Personal Site] [Blog] [Facebook]

Answer 4

>> Let's say at the beginning of a stored procedure I do this:  DECLARE @hasActualHours BIT

I'll then employ logic to set this local variable to either 0 or 1.

I'm interested to know if I can then use it to either include or exclude entire "AND..." clauses within my WHERE clause? <<

No, no, no, no!  Your whole approach is totally wrong. Not a little wrong, but totally wrong. You just heard that from someone who helped write the SQL Standards, has 1200+ articles and 10 books on this topic. Please listen. 

SQL is a declarative language, not a procedural flow control language. Declarative languages have no flow to control; there is no IF-THEN-ELSE, no loops. 

SELECT ...
  FROM … 
 WHERE Clause 1... 
   AND Clause 2... 
   AND <expression for actual hour count> > 0.00;

>> Is it possible to do something like that in T-SQL? <<

Sure, we can kludge it! But first post a statement for your boss to see that you do not care about maintaining code, performance and professionalism. This would be bad for your career; like saying you eat babies on a parenting forum :) That is what the other replies are! 

right now, you are the kid on a baseball diamond asking by which end do you hold the umpire. You are not thinking in SQL yet. My rule of thumb is you need a full year of SQL with a good mentor (read that last phrase again). It is a different mindset. 

I wrote a book, THINKING IN SETS, to help with the change. See if that helps. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL