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

by

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

More Related Contents:

  1. Add a column with a default value to an existing table in SQL Server
  2. How to Join to first row
  3. Pivot using SQL Server 2000
  4. How to get the first and last date of the current year?
  5. How Can I Sort A ‘Version Number’ Column Generically Using a SQL Server Query
  6. Perform regex (replace) in an SQL query
  7. Find last sunday
  8. Simulating group_concat MySQL function in Microsoft SQL Server 2005?
  9. How can I do an UPDATE statement with JOIN in SQL Server?
  10. Why does NULL = NULL evaluate to false in SQL server
  11. The multi-part identifier could not be bound
  12. How to insert a line break in a SQL Server VARCHAR/NVARCHAR string
  13. SQL Query to get aggregated result in comma separators along with group by column in SQL Server
  14. Fastest way to update 120 Million records
  15. Calendar Table – Week number of month
  16. how can I Update top 100 records in sql server
  17. Count Number of Consecutive Occurrence of values in Table
  18. Is there a SELECT … INTO OUTFILE equivalent in SQL Server Management Studio?
  19. How can I compare time in SQL Server?
  20. SQL – Difference between COALESCE and ISNULL? [duplicate]
  21. SQL: capitalize first letter only [duplicate]
  22. UPDATE and REPLACE part of a string
  23. Improve SQL Server query performance on large tables
  24. T-SQL Skip Take Stored Procedure
  25. Database Naming Conventions by Microsoft?
  26. Functions vs Stored Procedures
  27. Convert date to YYYYMM format
  28. How to get results in a custom order? [closed]
  29. Backup SQL Schema Only?
  30. SQL Server: +(unary) operator on non-numeric Strings

Leave a Comment