SQL Server 2008 - boolean variable change query

Question

I have the following query:

DECLARE @IsStocked bit
SELECT * FROM Products p WHERE p.LastSeen >  GETDATE() - 30 

This returns all Products that have been seen within the last 30 days.

My question is, I would like the p.LastSeen > GETDATE() - 30 clause to only apply when @IsStocked = true.

This is part of a larger query, and I'd like to achieve this without using IF/ELSE statements (i.e. if @IsStocked = false the p.LastSeen > GETDATE() - 30 section of the WHERE clause is ignored completely).

Thanks!

Answer 1

DECLARE @IsStocked bit;

SELECT * 
FROM Products p
WHERE (@IsStocked = 1 AND p.LastSeen > GETDATE() - 30) 
OR     @IsStocked = 0;

Answer 2

DECLARE @IsStocked bit

SELECT * FROM Products p
WHERE  @IsStocked = 0 OR (p.LastSeen > GETDATE() - 30);

Answer 3

Here is my Query according to your question

DECLARE @IsStocked bit
Set @IsStocked=1
Select * from Product
Where LastSeen  = Case When (@IsStocked=1) THEN GETDATE() - 30 
                   Else LastSeen  END

hope that will help.