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!

why don'y you want to use an IF/ELSE? The other alternative might lead to an incorrectly cached query plan.... — Mitch Wheat
– Mitch Wheat, Commented Sep 20, 2009 at 10:21
for simplicity really - there's around 10 variables in the sproc - all of which can be true/false or null - unless there is a way you can suggest that's not going to result in a IF/ELSE for each? thanks! — db1234
– db1234, Commented Sep 20, 2009 at 10:28

Mitch Wheat · Accepted Answer · 2009-09-20 10:22:24Z

4

DECLARE @IsStocked bit

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

answered Sep 20, 2009 at 10:22

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dave Markle · Accepted Answer · 2009-09-20 10:20:24Z

4

DECLARE @IsStocked bit;

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

answered Sep 20, 2009 at 10:20

Dave Markle

98.3k20 gold badges152 silver badges172 bronze badges

1 Comment

Dave Markle Over a year ago

@Mitch: It's 6:30 AM and I can't get back to sleep. Gimme a break, man! ;-)

Asim Sajjad · Accepted Answer · 2009-09-20 10:49:47Z

1

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.

answered Sep 20, 2009 at 10:49

Asim Sajjad

2,54411 gold badges36 silver badges76 bronze badges

Collectives™ on Stack Overflow

SQL Server 2008 - boolean variable change query

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related