0

I'm attempting to create a CASE when my @ReportId = A then return a list of values. Else @reportId = @reportId

I've attempted to wrong around this by possibly listening every condition but this issue is that I would like all of these ids returned when @ReportId = 'A'

How can I achieve this in my SQL Case?

SELECT RT.ReportTitle, RT.ReportCode, RT.ReportID, 
FROM dbo.reportTitles As RT
WHERE LEFT(RT.ReportCode, 2) = 
    CASE 
    WHEN @ReportID= 'A' THEN (01, 02, 03, 04, 05, 06, 07, 08, 09)
    ELSE @ReportID
    END
ORDER BY RT.ReportTitleASC

My expected outcome would be to Select from dbo.reportTitles Where @ReportId are LIKE the list of ids when @ReportID = 'A'

If it is not 'A' I'd like it to be using the original value that ReportID is.

Dale K
  • 25,246
  • 15
  • 42
  • 71
stepheniok
  • 395
  • 3
  • 16
  • I'm not clear what results you want when `@ReportID <> 'A'` – Dale K Jun 28 '21 at 23:17
  • 1
    If `@ReportID` does not equal `'A'` i'd like ReportID just to be the value that is passed. In every situation it's currently an integer but one where the `ReportID` = `'A'` so I'm attempting to create the case to return the list of ids when it is `'A'` and just return its original value when it's not. Hope that clears it up – stepheniok Jun 28 '21 at 23:21
  • 1
    You can use a `case` expression in a `where` clause, as shown in [this](https://stackoverflow.com/a/10260297/92546) answer for an `on` clause. That said, Dale K's answer is easier. – HABO Jun 29 '21 at 00:24

1 Answers1

4

Just use regular AND/OR logic... no need for a case.

WHERE (
    @ReportID = 'A'
    AND LEFT(RT.ReportCode, 2) IN (01, 02, 03, 04, 05, 06, 07, 08, 09)
) OR (
    @ReportID = CAST(LEFT(RT.ReportCode, 2) AS VARCHAR(2))
)

Note: This will only handle ReportCodes up to 99... if you need more you'll need to increase the sizes.

Dale K
  • 25,246
  • 15
  • 42
  • 71