title: SSRS Parameters. Allowing "All" or "Null"
body: <p>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. </p>
  
  <p>So, if I start out with code such as: </p>
  
  <pre><code>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' 
  </code></pre>
  
  <p>I want those parameters to be optional so my first attempts were to make the parameters default to  null such as:</p>
  
  <pre><code>and (myt.partnumber = (@PartNumber) or (@PartNumber) is null)
  </code></pre>
  
  <p>That has problems because if the database fields in question are nullable then you will drop records because null does not equal null. </p>
  
  <p>I then used code such as this: </p>
  
  <pre><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')
  </code></pre>
  
  <p>That doesn't work because Year(myt.date) is an integer and @Year is not. </p>
  
  <p>So, here are my questions.</p>
  
  <ol>
  <li>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? </li>
  <li>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  </li>
  </ol>