1

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. :\

  • 1
    ([TBL_OUTBOUND_REVIEW].[ERROR_FREE] = @Status OR ([TBL_OUTBOUND_REVIEW].[ERROR_FREE] IS NULL AND @Status IS NULL))? – Rachel Apr 23 '13 at 18:14
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.