Say I have the following:
SELECT *
FROM Xyz
WHERE (@a IS NULL OR a = @a) AND
(@b IS NULL OR b = @b) AND
(@c IS NULL OR c = @c)
What I want to accomplish is to change the WHERE clause to use the conditions in a sort of COALESCE
fashion where it would use the first condition that was not null or it ran out conditions.
In pseudo speak it would be something like:
WHERE IF @a IS NOT NULL THEN
(
a = @a
)
ELSE IF @b IS NOT NULL THEN
(
b = @b
)
ELSE IF @c IS NOT NULL THEN
(
c = @c
)
Is this possible?
This will treat NULLs as wildcards:
SELECT *
FROM MyTable
WHERE COALESCE(
NULLIF(@a,a),
NULLIF(@b,b),
NULLIF(@c,c)
) IS NULL
This will use the first non-null condition:
SELECT *
FROM MyTable
WHERE EXISTS (
SELECT id,value
FROM (VALUES (1,a),(2,b),(3,c)) t1(id,value)
INTERSECT
SELECT TOP 1 id,comparator
FROM (VALUES (1,@a),(2,@b),(3,@c)) t(id,comparator)
WHERE comparator IS NOT NULL
ORDER BY id
)
There may be a more elegant way, but this should respect the desired logic
where
(@a is not null and @a = a) or
(@a is null and @b is not null and @b = b) or
(@a is null and @b is null and @c is not null and @c = c)
which can be (a bit) simplified to:
where
(@a = a) or
(@a is null and @b = b) or
(@a is null and @b is null and @c = c)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With