How to add this condition into where clause? Thanks.

Question

Post reply

How to add this condition into where clause? Thanks.

halifaxdal

SSCoach

Points: 19741
More actions
September 12, 2013 at 7:44 am

#277122

I need to have a query like:
declare @dt bit
select * from document where case @dt when 1 then doctype in (1, 2) when 0 then doctype = 1 end
How do I write the query? Thank you in advance

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

This should work.

where doctype = 1

or doctype = case @dt when 1 then 2 end

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

halifaxdal SSCoach Points: 19741 More actions · Answer 2

Sean Lange (9/12/2013)
This should work.
where doctype = 1
or doctype = case @dt when 1 then 2 end

That was simpler, isn't it? Thank you very much, I was thinking in a too complex way

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Or this?

SELECT *

FROM document

WHERE (@dt = 1 AND doctype IN (1, 2))

OR (@dt = 0 AND doctype = 1)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden