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
Please clarify this - do you want this behaviour, or is this behaviour occurring and not desirable?
Your AND / OR precedence isn't correct. Bracket the OR's before the AND:
Note however that the comparison will only work with a fixed number of digits on the Customer Number, e.g. comparing
F51
toF51111
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?