0

I'm trying to figure out how to filter by a date/get all options without a date ONLY WHEN @getdisabledoptions = 0, otherwise if @getdisabledoptions = 1 I want to get all of the options.

WHERE AffiliationID = @AffiliationID
AND  
case

this is the case that I want to filter by disabled date or get null values if @getdisabledoptions = null

when @GetDisabledOptions = 0 then DisabledDate > GETDATE() or DisabledDate = IS NULL

otherwise here I want to just pull everything where AffiliationId = @affiliationId and not filter by date

else end

What am I doing wrong here? I've tried to look up other answers but I'm just not wording something right.

Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
William
  • 23
  • 4
  • Also, is there a way to do this without a case statement? I'd like to know both ways if possible. – William Jan 14 '20 at 22:48
  • `else 1 end` also there shouldn't be an `=` in `DisabledDate = IS NULL` – Nick Jan 14 '20 at 22:50
  • @Nick still getting "invalid syntax near >" – William Jan 14 '20 at 22:54
  • 1
    `CASE` **expression**. `CASE` returns a single atomic value: [CASE (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-ver15). It's not a `Switch` statement (`Case` statements do not exist in T-SQL). Don't use a `CASE` **expression** in your `WHERE` though, use proper boolean logic with `AND` and `OR`. – Thom A Jan 14 '20 at 23:05
  • The tag you have used (`case`) also tells you this: *"In SQL, the CASE expression evaluates a list of conditions, returning the result for the first condition which evaluates to true. For programming languages' CASE questions, use the [switch-statement] tag instead."* – Thom A Jan 14 '20 at 23:07
  • Apart from there is a prior `AND`, @Nick . So, regardless of the value of `@AffiliationID` you would end up with any row that has a value of `NULL` for `DisabledDate`, for example. I actually thought you had `@GetDisabledOptions = 0` at first, but actually the whole thing should be wrapped in parenthesis if you're using `1`. – Thom A Jan 14 '20 at 23:12
  • 1
    @Larnu you're right, the entire expression should be enclosed in parentheses i.e. `(@GetDisabledOptions = 1 or DisabledDate > GETDATE() or DisabledDate IS NULL)` – Nick Jan 14 '20 at 23:13
  • @CodyStorch [This](https://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition/10260297#10260297) answer demonstrates how to use `case` in an `on` clause, similar to a `where` clause. As others have suggested, you don't need it for the logic you provided and it interferes with the query optimizer. – HABO Jan 14 '20 at 23:21
  • No, I only want to use the filter " AND disableddate > getdate() or disableddate IS NULL" if the @getdisabledoptions = 0. – William Jan 14 '20 at 23:23

3 Answers3

0

Based on the logic you have described you can simplify your WHERE expression to:

WHERE AffiliationID = @AffiliationID
AND (@GetDisabledOptions = 1 or DisabledDate > GETDATE() or DisabledDate IS NULL)
Nick
  • 138,499
  • 22
  • 57
  • 95
  • Sorry, no, I want to filter by the disabled date only if the @GetDisabledOptions = 1. – William Jan 14 '20 at 23:20
  • @CodyStorch that's not what you say in your question: "@getdisabledoptions = 1 I want to get all of the options" – Nick Jan 14 '20 at 23:22
  • @CodyStorch did you try this? If you **do** want to filter by the date if `@GetDisabledOptions = 1` then just change `@GetDisabledOptions = 1` in the query to `@GetDisabledOptions = 0` – Nick Jan 15 '20 at 05:25
0

You can use Boolean expression instead of case : 

where (@GetDisabledOptions = 0 and DisabledDate > GETDATE()) or
      (@GetDisabledOptions = 1 and AffiliationID = @AffiliationID);
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
-1

try this 

WHERE AffiliationID = @AffiliationID
AND ( @GetDisabledOptions = 1 OR (DisabledDate > GETDATE() or DisabledDate = IS NULL))
Arpit Srivastava
  • 9
  • 2