How to simulate COALESCE conditions in WHERE clause

sql-server, sql-server-2012, t-sql

Solution

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
)

Problem

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?

Original source