Tsql between two dates that may be null

Question

I am new to TSQL. I need to write a stored procedure that returns filtered records. Thus, the user has two dates to enter. He can enter only one date, or two, or both.

In saved proc I have two parameters @From, @To - date type. I need to search for entries.

If the user did not enter dates, I do not need dates in the select request.

If the user entered 2 dates - I need to search for them inclusively.

If the user entered the date @From - I need to search until today inclusive.

If the user entered the date @ - I need to search for dates less than @To.

Thank you for your help.

Answer 1

SELECT ColumnList
FROM MyTable
WHERE
    (@FromDate IS NULL OR FromDateColumn >= @FromDate)
    AND (@ToDate IS NULL OR ToDateColumn <= @ToDate )

(But keep in mind that this may suffer from the consequences of an inappropriate cached query plan due to sniffing parameters, especially if the proposal WHEREhas a large number of parameters)

Answer 2

SELECT 
   Columns
FROM  
   Table
WHERE 
   DateColumn BETWEEN (CASE WHEN @FromDate IS NULL THEN DateColumn ELSE @FromDate END)
   AND (CASE WHEN @ToDate IS NULL THEN DateColumn ELSE @ToDate END)