Get all data except the value on the first case statement-CodePudding

I have a simple select like this:

DECLARE @ReportType AS VARCHAR(255) = 'Machine'

 SELECT
   [WOCust3] AS [Machine]
  FROM MyTable WHERE
   (@ReportType IS NULL
             OR  [WOCust3] LIKE (CASE WHEN @ReportType = 'Slack'
             THEN '%Slack test%'
             ELSE '%'
             END
             ))

As you can see, I use the case to compare if the variable equals Slack then filter by that field; the problem is in the else clause. When the report type equals to another string it is throwing all results including Slack test results, and I want to get all except for slack test results, how can I achieve that?

CodePudding user response：

I re-worked your CASE expression to better handle instances where the @ReportType is not Slack.

SQL:

DECLARE @ReportType AS VARCHAR(255) = 'Machine'

SELECT 
  a.Machine_Name
FROM 
  Machine_Data a 
WHERE 
  a.Machine_Name IN (
    CASE 
    WHEN @ReportType = 'Slack' AND a.Machine_Name LIKE '%Slack test%' THEN a.Machine_Name 
    WHEN ISNULL(@ReportType, 'N/A') <> 'Slack' AND a.Machine_Name NOT LIKE '%Slack test%' THEN a.Machine_Name
    END)

Result with Machine or NULL as ReportType:

| Machine_Name |
|--------------|
|    Machine 1 |
|    Machine 2 |
|        Other |
|         Test |
|          Dev |

Result with Slack as ReportType:

| Machine_Name |
|--------------|
|   Slack test |

SQL Fiddle:

http://sqlfiddle.com/#!18/ca614/13