CODEWITHSUNDEEP
sql
Rudolf Lamprecht
Rudolf Lamprecht

Reputation: 1070

Determine if two parameters are null, then use parameters in the where clause

Good day, I receive 2 nullable date parameters in my stored procedure. @StartDate and @EndDate. I need to execute my procedure as normal if these two parameters are NULL, else the @StartDate param needs to be >= my StartDateTime value AND @EndDate param needs to be <= my EndDateTime value. Below is a snippet of what I am trying to accomplish but are not sure of the syntax.

    FROM DI_Intervention_Schedule S
    WHERE
        (
            @ID IS NULL
        OR  S.[ID] = @ID
        )
    AND (
            CASE @StartDate WHEN IS NOT NULL THEN
                @StartDate >= S.[StartDateTime] AND @EndDate <= S.[EndDateTime]
            END
        )

Any help please?

Upvotes: 0

Views: 79

Answers (1)

user2063626
user2063626

Reputation:

Try below code :

 FROM DI_Intervention_Schedule S
    WHERE
    (S.[ID] = @ID OR @ID IS NULL)
    AND (( S.[StartDateTime] >= @StartDate   AND S.[EndDateTime] <= @EndDate)  OR @StartDate IS NULL )

Upvotes: 1

Related Questions