WHERE IS NULL IS NOT NULL or NO WHERE clause depending on SQL Server parameter value

Question

I have a stored procedure in SQL Server 2000 that performs a search based on parameter values  For one of the parameters passed in  I need a different WHERE clause depending on its value - the problem is that the 3 values would be where MyColumn    IS NULL  IS NOT NULL ANY VALUE  NULL AND NOT NULL   essentially no WHERE clause    I m having some mental block in coming up with the correct syntax  Is this possible to do in one select statement without performing some IF  parameter BEGIN     END branching

User · Answer

An other way of CASE   SELECT     FROM MyTable WHERE 1   CASE WHEN  myParm   value1 AND MyColumn IS NULL     THEN 1                 WHEN  myParm   value2 AND MyColumn IS NOT NULL THEN 1                 WHEN  myParm   value3                          THEN 1            END

User · Answer

This kind of logic could be implemented using EXISTS   CREATE TABLE tab a INT  b VARCHAR 10    INSERT INTO tab a b  VALUES 1  a    1  NULL   NULL   a    2  b      Query   DECLARE  a INT   --SET  a   1     -- specific NOT NULL value --SET  a   NULL  -- NULL value --SET  a   -1    -- all values  SELECT   FROM tab t WHERE EXISTS SELECT t a INTERSECT SELECT  a UNION SELECT  a WHERE  a    -1      db lt  fiddle demo  It could be extended to contain multiple params   SELECT   FROM tab t WHERE EXISTS SELECT t a INTERSECT SELECT  a UNION SELECT  a WHERE  a    -1     AND EXISTS SELECT t b INTERSECT SELECT  b UNION SELECT  a WHERE  b    -1

User · Answer

WHERE MyColumn   COALESCE  value MyColumn      If  value is NULL  it will compare MyColumn to itself  ignoring  value   no where clause  IF  value has a value  NOT NULL  it will compare MyColumn to  value    Reference  COALESCE  Transact-SQL

User · Answer

You could just do something like this   SELECT   FROM foo WHERE   param   0 AND MyColumn IS NULL  OR   param   1 AND MyColumn IS NOT NULL  OR   param   2    Something like that

User · Answer

This is how it can be done using CASE   DECLARE  myParam INT  SET  myParam   1   SELECT      FROM MyTable  WHERE  T    CASE  myParam              WHEN 1 THEN                  CASE WHEN MyColumn IS NULL THEN  T  END              WHEN 2 THEN                 CASE WHEN MyColumn IS NOT NULL THEN  T  END              WHEN 3 THEN  T  END

User · Answer

I ve had success with this solution   It s almost like Patrick s  with a little twist   You can use these expressions separately or in sequence   If the parameter is blank  it will be ignored and all values for the column that your searching will be displayed  including NULLS   SELECT   FROM MyTable WHERE      --check to see if  param1 exists  if  param1 is blank  return all     --records excluding filters below  Col1 LIKE        param1       OR  param1       AND     --where you want to search multiple columns using the same parameter     --enclose the first  OR  expression in braces and enclose the entire      --expression    Col2 LIKE        searchString       OR Col3 LIKE        searchString        OR  searchString       AND     --if your search requires a date you could do the following  Cast DateCol AS DATE  BETWEEN CAST  dateParam AS Date  AND CAST GETDATE   AS DATE  OR  dateParam

User · Answer

Here is how you can solve this using a single WHERE clause   WHERE   myParm   value1 AND MyColumn IS NULL  OR    myParm   value2 AND MyColumn IS NOT NULL  OR    myParm   value3    A na  ve usage of the CASE statement does not work  by this I mean the following   SELECT Field1  Field2 FROM MyTable WHERE CASE  myParam     WHEN value1 THEN MyColumn IS NULL     WHEN value2 THEN MyColumn IS NOT NULL     WHEN value3 THEN TRUE END   It is possible to solve this using a case statement  see onedaywhen s answer

[sql] WHERE IS NULL, IS NOT NULL or NO WHERE clause depending on SQL Server parameter value

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2000