February 16, 2026 at 9:12 pm
Thanks in advance for any clues on this.
I am trying to write a 'conditional where' statement in a typical select statement.
Early in the stored procedure the variable @Type is set to either 'AB or 'CD'.
I'm trying to have my WHERE be dependent on that variable e.g.:
WHEN @Type = 'AB' my "WHERE" needs TO be "WHERE PlanDescription IN ('DEF','GHI')"
WHEN @Type = 'CD' my "WHERE" needs TO be "WHERE PlanDescription IN ('UVW','XYZ')"
I've tried several combinations of CASE, IIF, etc. but no luck yet... trying to avoid Dynamic SQL if possible
which intuitively seems like overkill.
Thanks!
February 16, 2026 at 9:17 pm
WHERE
(@Type = 'AB' AND PlanDescription IN ('DEF','GHI') OR
@Type = 'CD' AND PlanDescription IN ('UVW','XYZ')
)
February 16, 2026 at 9:21 pm
Thank you!! works great
February 16, 2026 at 10:01 pm
For clarity, and potentially accuracy for more complex "OR" conditions, I suggesting another set of parentheses:
WHERE
((@Type = 'AB' AND PlanDescription IN ('DEF','GHI')) OR
(@Type = 'CD' AND PlanDescription IN ('UVW','XYZ'))
)
Yes, the original works fine because ANDs evaluate before OR, but if you ever need to add an "OR" to the other conditions for some reason, you could get unexpected results.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply