Reputation: 59
Inside my procedure I have a where statement to either show all records where datecomplete is inside selected parameters or if the parameters aren't set just show all records regardless of if they have any data in the column datecomplete.
I've attempted to achieve this with the below code:
WHERE (jobsToCheck.DateCompleted =
CASE WHEN @StartDateCompleted IS NULL THEN jobsToCheck.DateCompleted IS NULL
ELSE (jobsToCheck.DateCompleted BETWEEN @StartDateCompleted AND
@EndDateCompleted OR @StartDateCompleted IS NULL) END)
But get the error 'Incorrect syntax near the keyword 'IS'.' Is there a better way of doing this?
The closest I've got to desired results is using the below:
(jobsToCheck.DateCompleted BETWEEN @StartDateCompleted AND @EndDateCompleted
OR @StartDateCompleted IS NULL) OR jobsToCheck.DateCompleted IS NULL
However this shows all records with or without a date complete when I set the data StartDate and EndDate parameters, but I only want records with a datecomplete between the start date and end date or if I don't set a startdate or end date all records with no datecompleted.
Upvotes: 0
Views: 126
Reputation: 1270401
It is best to avoid case
expressions in a where
clause, especially when you can do the same thing with regular boolean logic:
WHERE (jobsToCheck.DateCompleted >= @StartDateCompleted OR @StartDateCompleted IS NULL) AND
(jobsToCheck.DateCompleted <= @EndDateCompleted OR @EndDateCompleted IS NULL) OR
jobsToCheck.DateCompleted IS NULL
Upvotes: 1
Reputation: 273
It is wrong that your second "IS NULL" expression. I mean you should change your statement to this:
WHERE (jobsToCheck.DateCompleted = CASE WHEN @StartDateCompleted IS NULL THEN ISNULL(jobsToCheck.DateCompleted, 'value that whatever you want') ELSE (jobsToCheck.DateCompleted BETWEEN @StartDateCompleted AND @EndDateCompleted OR @StartDateCompleted IS NULL) END)
Upvotes: 0