0

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.

Olliecycles
  • 59
  • 5
  • Sample data and desired results would really help. – Gordon Linoff Aug 02 '18 at 13:10
  • Although [this answer](https://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition/10260297#10260297) applies to a `join` condition, it works the same way with `where`. – HABO Aug 03 '18 at 02:31

2 Answers2

1

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
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This doesn't quite work though, when the parameters are set I get the results I would expect but when the parameters are null I get no records back but I want to see all records where DateCompleted is NULL – Olliecycles Aug 02 '18 at 12:46
0

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)

Keyyubi
  • 247
  • 1
  • 3
  • 13
  • I then get the error Incorrect syntax near the keyword 'BETWEEN' – Olliecycles Aug 02 '18 at 13:05
  • you can change there like this: `(SELECT jobsToCheck.DateCompleted FROM "TableName" WHERE jobsToCheck.DateCompleted BETWEEN @StartDateCompleted AND @EndDateCompleted OR @StartDateCompleted IS NULL)` carefull with parentheses – Keyyubi Aug 02 '18 at 13:40
  • What kind values should your WHERE condition have? plural or single value? – Keyyubi Aug 02 '18 at 13:42