SQL - Creating a stored procedure with multiple optional parameters

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.

A Reference Number Range - Held in Table 2 - (Optional)

A Customer Number Range - Held in Table 3 - (Optional)

A Date Range - Held in Table 4 - (Mandatory)

Either the Reference Number or the Customer Number must be entered

So far I have this

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)) >=  @fromDate
                AND
                CONVERT(DATETIME, CONVERT(CHAR(8), Table4.Date, 112) + ' ' + CONVERT(CHAR(8), Table4.Time, 108)) <= @toDate
            )
            AND

            Table4.Info = 1

            AND

            (
                (Table2.Ref >= @RefFrom) OR (@RefFrom IS NULL)
                AND
                (Table2.Ref <= @RefTo) OR (@RefTo IS NULL)
            )

            AND   

            (
                (Table3.Number >= @CustomerNumFrom) OR (@CustomerNumFrom IS NULL)
                AND
                (Table3.Number <= @CustomerNumTo) OR (@CustomerNumTo IS NULL)
                )   
            )

Am getting a few problems at the moment.

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,

The second thing which isn't working is when I enter a CustomerNumber range it returns CustomerNumers outside of the range specified.

Am really stuck on how to solve this problem if anyone could offer help

Thanks

Solution

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,

Please clarify this - do you want this behaviour, or is this behaviour occurring and not desirable?

The second thing which isn't working is when I enter a CustomerNumber range it returns CustomerNumers outside of the range specified.

Your AND / OR precedence isn't correct. Bracket the OR's before the AND:

(Table3.Number >= @CustomerNumFrom OR @CustomerNumFrom IS NULL)
AND
(Table3.Number <= @CustomerNumTo OR @CustomerNumTo IS NULL)

Note however that the comparison will only work with a fixed number of digits on the Customer Number, e.g. comparing F51 to F51111 won't end well.

One other point - doing CONVERT in the where clause like this will hurt performance (SARGability). It seems strange that the Date and Time have been separated in the data model - if you combine Date and Time into one column, you can do a direct date time comparison?