title: SQL - Creating a stored procedure with multiple optional parameters
body: <p>Am trying to create optional parameters in a stored procedure (SQL Server 2012) which will allow a user to be able to select the following.</p>
  
  <p>A Reference Number Range - Held in Table 2 - (Optional)</p>
  
  <p>A Customer Number Range - Held in Table 3 - (Optional)</p>
  
  <p>A Date Range - Held in Table 4 - (Mandatory)</p>
  
  <p>Either the Reference Number or the Customer Number must be entered </p>
  
  <p>So far I have this</p>
  
  <pre><code>declare @RefFrom Varchar(50) = NULL --'F51'  
  declare @RefTo Varchar(50) = NULL --'F51' 
  declare @CustomerNumFrom Varchar(50) = NULL --'FH1' 
  declare @CustomerNumTo Varchar(50) = NULL --'FH1'
  declare @fromDate date -- Works for date ranges 
  declare @toDate date
  
  set @fromDate = '2014-10-01'
  set @toDate = '2014-11-05'
  
  set @toDate  = IIF(@toDate  is NULL,  @toDate , DATEADD(day,1,@toDate ))
  set @toDate  = IIF(@toDate  is NULL,  @fromeDate, @toDate )
  
  SELECT 
  
  Table2.Ref AS [Ref],
  Table3.Number AS [Customer Number],
  Table4.FromDate AS [Date],
  Table4.ToTime AS [Time],
  
  
  
  FROM Table1 
  INNER JOIN Table2 ON Table1.ID = Table2.ID 
  INNER JOIN Table3 ON Table2.ID = Table3.ID 
  INNER JOIN Table5 ON Table1.DatID = Table5.ID 
  INNER JOIN Table4 ON Table5.ID = Table4.ID 
  
  where Table1.StatID = 1
  
  AND Table4.ID
  IN (    
          select Table4.ID 
          from Table4
          where 
              (
                  CONVERT(DATETIME, CONVERT(CHAR(8), Table4.Date, 112) + ' ' + CONVERT(CHAR(8), Table4.Time, 108)) &gt;=  @fromDate
                  AND
                  CONVERT(DATETIME, CONVERT(CHAR(8), Table4.Date, 112) + ' ' + CONVERT(CHAR(8), Table4.Time, 108)) &lt;= @toDate
              )
              AND
  
              Table4.Info = 1
  
              AND
  
              (
                  (Table2.Ref &gt;= @RefFrom) OR (@RefFrom IS NULL)
                  AND
                  (Table2.Ref &lt;= @RefTo) OR (@RefTo IS NULL)
              )
  
              AND   
  
              (
                  (Table3.Number &gt;= @CustomerNumFrom) OR (@CustomerNumFrom IS NULL)
                  AND
                  (Table3.Number &lt;= @CustomerNumTo) OR (@CustomerNumTo IS NULL)
                  )   
              )
  </code></pre>
  
  <p>Am getting a few problems at the moment.</p>
  
  <p>The first thing which isn't working is that I can have nulls in both Reference and CustomerNumber and I'll still get data returned based on the date range,</p>
  
  <p>The second thing which isn't working is when I enter a CustomerNumber range  it returns CustomerNumers outside of the range specified. </p>
  
  <p>Am really stuck on how to solve this problem if anyone could offer help </p>
  
  <p>Thanks</p>