SQL Switch/Case in 'Where' Clause

SQL Switch/Case in 'where' clause

declare @locationType varchar(50);
declare @locationID int;

SELECT column1, column2
FROM viewWhatever
WHERE
@locationID = 
  CASE @locationType
      WHEN 'location' THEN account_location
      WHEN 'area' THEN xxx_location_area 
      WHEN 'division' THEN xxx_location_division 
  END

Switch case/If in a where clause

Try conditioning on the nullity of @SECOND instead of using CASE.

SELECT *
FROM BANK_DETAIL
WHERE
-- other conditions go here
AND ((@SECOND IS NULL AND X = @FIRST)
  OR (@SECOND IS NOT NULL AND X >= @FIRST AND X <= @SECOND))

SQL Switch/Case in 'where' clause with and

Since the left part remains the same and only the value can be different, you could try the following approach (CASE 'returns' the value of the right part):

AND DATEDIFF(DAY, btw.sell_date, GETDATE()) >= 
  CASE
    WHEN bd.shopping_code IN ('B41', 'B74', 'BT34') THEN 30
    ELSE @day_to_find
  END

SQL Switch/Case in where clause with NULL value

This is normally done with logic such as this:

where #{value} is NULL or #{value} = param

I assume #{value} is the value being passed in and param is some column in a table.

using case statement in a where clause

The branches of a case expression can only return values, not additional expressions to be evaluated in the where condition. You could, however, simulate this behavior with the and and or logical operators:

select    *
from      ##ScheduleDetail SD
left join ##HolidayFilterTbl HF on SD.Scheduledate = HF.Testdate
where     (ScheduleDate = testdate) and
          ((HF.IsHoliday = 1 and overtime = 1 and makeup = 0) or
           (overtime = 0 and Makeup = 0)) and
          DOW = 5 
order by  ActivityStartTime

Note that you have makeup = 0 on both branches of the case expression in the question (or both sides of the or in the answer), so you could extract it out of it and simplify the condition a bit:

select    *
from      ##ScheduleDetail SD
left join ##HolidayFilterTbl HF on SD.Scheduledate = HF.Testdate
where     ScheduleDate = testdate and
          makeup = 0 and
          ((HF.IsHoliday = 1 and overtime = 1) or
           overtime = 0) and
          DOW = 5 
order by  ActivityStartTime

SQL Server CASE Statement in side where clause switch operator

Just in case you don't have to use the CASE statement at all, the following will work, too:

...
WHERE(@WorkStatus = 1 AND [X].[EarlyEnd] < GETDATE())
     OR (@WorkStatus = 2 AND [X].[EarlyEnd] = GETDATE())
     OR (@WorkStatus = 3 AND [X].[EarlyEnd] > GETDATE());

Case statement in where clause in SQL Server if variable have any value otherwise compare current

try this way:

SELECT a.user_id, a.username, a.first_name, a.last_name, a.division_id, a.dept_id, a.email,
a.password, a.IsAdmin, a.status, a.cdate, a.mdate FROM Account AS a 
LEFT JOIN Roles r on a.user_id = r.user_id
WHERE ((ISNULL(@role_id,'') = '') OR (r.role_id = @role_id))

---

Related Topics