SSRS Parameters. Allowing "All" or "Null"

Question

SSRS parameters are a pain. I want to be able to re-use reports for many different needs by allowing the users access to many different parameters and making them optional.

So, if I start out with code such as:

Select * from mytable myt
where myt.date between '1/1/2010' and '12/31/2010'
and year(myt.date) = '2010'
and myt.partnumber = 'XYZ-123' 

I want those parameters to be optional so my first attempts were to make the parameters default to null such as:

and (myt.partnumber = (@PartNumber) or (@PartNumber) is null)

That has problems because if the database fields in question are nullable then you will drop records because null does not equal null.

I then used code such as this:

DECLARE @BeginDate AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @PartNumber AS VARCHAR(25)
SET @Year = '..All'
SET @BeginDate = '1/1/2005'
SET @EndDate = '12/31/2010'
SET @PartNumber = '..All'
SET @Year = '..All'

Select * from mytable myt
where (myt.date between (@BeginDate) and (@EndDate))
and (year(myt.date) =  (@Year) or (@Year) = '..All' )
and (myt.partnumber = (@PartNumber) or (@PartNumber) = '..All')

That doesn't work because Year(myt.date) is an integer and @Year is not.

So, here are my questions.

1. How can I make my dates optional? Is the best way to simply default them to dates outside of a practical range so I return all values?
2. What is the best way to handle the null or '..All' options to make my queries as readable as possible and allow my users to have optional parameters for most data types? I'd rather not use null for

Answer 1

Go ahead and allow nulls, which indicates the filter should not be applied. Then, you can use the following:

SELECT *
FROM mytable myt
WHERE COALESCE(myt.date, '1/1/1900') between COALESCE(@BeginDate, myt.date, '1/1/1900') and COALESCE(@EndDate, myt.date, '1/1/1900')
    AND COALESCE(YEAR(myt.date), -1) = COALESCE(@Year, YEAR(myt.date), -1)
    AND COALESCE(myt.partnumber, -1) = COALESCE(@PartNumber, myt.partnumber, -1)

In summary, if any variable value is NULL, then compare the column value to itself, which effectively ignores the condition. More specifically, when testing myt.date, if @BeginDate is NULL then set the lower range value equal to the myt.date value. Do the same substitution with the @EndDate value. Even, if both @BeginDate and @EndDate are NULL, the condition will be true.

A similar approach is used for YEAR(myt.date) and myt.partnumber. If the variable value is NULL, then compare the column value to itself, which is always true.

UPDATE: Added a default value to each COALESCE to handle the situation where the column value is NULL.