What I want is rows where opt_1 OR opt_2 OR opt_3 have a match (if any "@opt" values were specified) in addition to the "@cond" matches. Instead I get ALL rows that match for @cond regardless of the @opt values. If I change the OR to AND, I only get rows that include all of the specified @opt values.
(
@cond1 int = NULL,
@cond2 int = NULL,
@opt1 bit = NULL,
@opt2 bit = NULL,
@opt3 bit = NULL,
@cond3 int = NULL
)
AS
SELECT cond_1, cond_2, opt_1, opt_2, opt_3, cond_3
FROM theTable
WHERE
(@cond1 IS NULL OR cond_1 = @cond1)
AND (@cond2 IS NULL OR cond_2 = @cond2)
AND
(
(@opt1 IS NULL OR opt_1 = @opt1)
OR (@opt2 IS NULL OR opt_2 = @opt2)
OR (@opt3 IS NULL OR opt_3 = @opt3)
)
AND (@cond3 IS NULL OR cond_3 = @cond3)
View Complete Post