I need help coding for the where clause in a stored procedure passing in these input parameters:

DECLARE @UID_CUSTOMER           int = 7
    ,   @BOOL_HAS_DEFECT        bit = 0
    ,   @BOOL_HAS_INSPECTION    bit = 0
    ,   @DTTM_TRIP_START        datetime = '2024-01-01'
    ,   @DTTM_TRIP_END          datetime = '2024-06-01'
    ;

In the T-SQL query, I would use this WHERE clause:

WHERE ... other conditions ...
  AND (PTI.UID_PTINSPECTION IS NOT NULL)
  AND (TMD.UID_TMDEFECT IS NOT NULL);

However, I want to use the inbound parameter @BOOL_HAS_INSPECTION as here:

  AND (PTI.UID_PTINSPECTION (CASE WHEN @BOOL_HAS_INSPECTION = 1 
                        THEN (IS NOT NULL)
                        ELSE (IS NULL) END))

This code fragment is invalid.

Question: how can I use the @BOOL_HAS_INSPECTION to properly specify IS NOT NULL and/or IS NULL?

I need help coding for the where clause in a stored procedure passing in these input parameters:

DECLARE @UID_CUSTOMER           int = 7
    ,   @BOOL_HAS_DEFECT        bit = 0
    ,   @BOOL_HAS_INSPECTION    bit = 0
    ,   @DTTM_TRIP_START        datetime = '2024-01-01'
    ,   @DTTM_TRIP_END          datetime = '2024-06-01'
    ;

In the T-SQL query, I would use this WHERE clause:

WHERE ... other conditions ...
  AND (PTI.UID_PTINSPECTION IS NOT NULL)
  AND (TMD.UID_TMDEFECT IS NOT NULL);

However, I want to use the inbound parameter @BOOL_HAS_INSPECTION as here:

  AND (PTI.UID_PTINSPECTION (CASE WHEN @BOOL_HAS_INSPECTION = 1 
                        THEN (IS NOT NULL)
                        ELSE (IS NULL) END))

This code fragment is invalid.

Question: how can I use the @BOOL_HAS_INSPECTION to properly specify IS NOT NULL and/or IS NULL?

• sql
• sql-server
• t-sql

Share Improve this question Follow edited Feb 4 at 19:05 Dale K 27.5k1515 gold badges5858 silver badges8383 bronze badges asked Feb 4 at 18:03 John DJohn D 68122 gold badges1010 silver badges2727 bronze badges 5

• 1 AND / OR logic. – Dale K Commented Feb 4 at 18:08
• 2 For performance reasons it's often better to use two entirely separate queries, or use dynamic SQL. – Charlieface Commented Feb 4 at 18:29
• Read union all ^^^ – Dale K Commented Feb 4 at 18:44
• 2 fwiw, that is not the correct format for date literals in SQL Server, and there are circumstances where they could be interpreted incorrectly. For historical reasons, SQL Server should use the unseparated variant of ISO8601 for the date portion of datetime literals (ie, yyyyMMdd instead of yyyy-MM-dd) – Joel Coehoorn Commented Feb 4 at 19:05
• Thank you for suggestion. I shall use this in the future. It worked for both SQL-type as 'date' and as 'datetime'. Thank you. – John D Commented Feb 4 at 20:40

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 7

This will work, but there may be a more concise way.

WHERE ... AND
( (@BOOL_HAS_INSPECTION = 1 AND PTI.UID_PTINSPECTION IS NOT NULL)
OR (@BOOL_HAS_INSPECTION = 0 AND PTI.UID_PTINSPECTION IS NULL) )

For interest, some other possibilities:

1 - A shorter version, though possibly harder to understand (assumes @BOOL_HAS_INSPECTION will not be passed as NULL):

AND
     @BOOL_HAS_INSPECTION = IIF(PTI.UID_PTINSPECTION IS NULL, 0, 1) 

2 - A rather long winded version using nested CASE expressions. Possibly overkill in your specific case, but a technique which can come in handy when requirements are more complex:

AND
1 = (CASE WHEN @BOOL_HAS_INSEPECTION = 1 THEN CASE WHEN PTI.UID_PTINSPECTION IS NOT NULL THEN 1 END
          ELSE CASE WHEN PTI.UID_PTINSPECTION IS NULL THEN 1 END
     END)

Check your query plans if there are any performance concerns.