SSRS options. All or Zero Resolution

Question

SSRS options are a pain. I want to be able to reuse reports for many different needs, allowing users to access many different parameters and make them optional.

So, if I start with the code, for example:

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 these parameters to be optional, so my first attempts were to make the default parameters equal to zero, for example:

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

This has problems, because if the data fields in question are NULL, you discard the records since null is not null.

Then I used the following code:

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')

This does not work, because Year (myt.date) is an integer, and @Year is not.

So here are my questions.

• ? , ?
• "..All", ? null

Answer 1

. , WHERE -int . AND : T-SQL, :

and 1 = CASE @Year WHEN '..All' THEN 1 ELSE CASE WHEN year ( myt.date )  = CONVERT ( int, @Year ) THEN 1 ELSE 0 END END

"..All" int. . partnumber.

Answer 2

null, , . :

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)

, - NULL, , . , myt.date, @BeginDate NULL, , myt.date. @EndDate. @BeginDate @EndDate NULL, .

YEAR(myt.date) myt.partnumber. NULL, , .

UPDATE: COALESCE , NULL.

Answer 3

, , sql- , .

Select * from mytable myt
where (myt.date between (@BeginDate) and (@EndDate))
and (@Year IS NULL OR COALESCE(myt.date,'1900') = @Year)
and (@PartNumber IS NULL OR ISNULL(myt.partnumber, '<NULL>') = (@PartNumber)