T-sql – Conditional Where Statement Involving a NULL value

cnullt-sql

Trying to find a way to pass off a third value within a 'bit' field. I would like to be able to utilize NULL as a searchable value. As in 'True', 'False', or 'NULL', the only issue is I'm unsure how to go about it. My initial thoughts were some kind of 'if' branch within the statement, but it doesn't appear to allow this kind of syntax. Here is my current WHERE clause. I am trying to make it possible to have [ERROR_FREE] registered as '0', '1' or 'NULL' based on a variable passed from my C# application.

WHERE [TBL_OUTBOUND_REVIEW].[ACTIVE_DIRECTORY] LIKE @UserName AND
      CONVERT(date, [TBL_OUTBOUND_REVIEW].[DATE_SUBMITTED]) LIKE @DateSubmitted AND
      [TBL_OUTBOUND_REVIEW].[ERROR_FREE] = @Status AND
      [TBL_OUTBOUND_REVIEW].[LOCATION] LIKE @Location AND
      [TBL_OUTBOUND].[UDF_0] LIKE @UDF0
ORDER BY [TBL_OUTBOUND_REVIEW].[DATE_SUBMITTED] DESC;

More or less I'd like to accomplish this :

WHERE [TBL_OUTBOUND_REVIEW].[ACTIVE_DIRECTORY] LIKE @UserName AND
      CONVERT(date, [TBL_OUTBOUND_REVIEW].[DATE_SUBMITTED]) LIKE @DateSubmitted AND
              IF @Status IS NULL
               [TBL_OUTBOUND_REVIEW].[ERROR_FREE] IS NULL AND
              ELSE
               [TBL_OUTBOUND_REVIEW].[ERROR_FREE] = @Status AND
      [TBL_OUTBOUND_REVIEW].[LOCATION] LIKE @Location AND
      [TBL_OUTBOUND].[UDF_0] LIKE @UDF0
ORDER BY [TBL_OUTBOUND_REVIEW].[DATE_SUBMITTED] DESC;

… Though I know that doesn't work. :\

Best Answer

Use an OR to check if either the parameter matches the value, or both the parameter and the value are NULL, like this:

WHERE 
(
    [TBL_OUTBOUND_REVIEW].[ERROR_FREE] = @Status 
    OR 
    ([TBL_OUTBOUND_REVIEW].[ERROR_FREE] IS NULL AND @Status IS NULL)
)

You could also use a CASE statement as your IF if you really wanted to, but I think it's easier to read and understand the OR syntax above, so would prefer to use that.

WHERE 
(
    CASE WHEN @Status IS NULL AND [TBL_OUTBOUND_REVIEW].[ERROR_FREE] IS NULL THEN 1
       WHEN [TBL_OUTBOUND_REVIEW].[ERROR_FREE] = @Status THEN 1
       ELSE 0 END
) = 1
Related Question