Case in Where, SQL Server

CASE in WHERE, SQL Server

A few ways:

-- Do the comparison, OR'd with a check on the @Country=0 case
WHERE (a.Country = @Country OR @Country = 0)

-- compare the Country field to itself
WHERE a.Country = CASE WHEN @Country > 0 THEN @Country ELSE a.Country END

Or, use a dynamically generated statement and only add in the Country condition if appropriate. This should be most efficient in the sense that you only execute a query with the conditions that actually need to apply and can result in a better execution plan if supporting indices are in place. You would need to use parameterised SQL to prevent against SQL injection.

SQL Where Clause with CASE & NOT IN Condition

You can use CASE in WHERE as Shown below:

    WHERE 1=(
             CASE WHEN @X = 0 and @y = 0 and pagecode not in ('admin','external') THEN 1
                  WHEN @x = 0 and @y = 1 and pagecode not in ('admin') THEN 1
                  WHEN @x = 1 and @y = 0 and pagecode not in ('external') THEN 1
                  ELSE 0 END
            )

this will not return any row if @x=1 and @y=1.
If you want to return all rows if @x=1 and @y=1

    WHERE 1=(
             CASE WHEN @X = 0 and @y = 0 and pagecode not in ('admin','external') THEN 1
                  WHEN @x = 0 and @y = 1 and pagecode not in ('admin') THEN 1
                  WHEN @x = 1 and @y = 0 and pagecode not in ('external') THEN 1
                  WHEN @x = 1 and @y = 1 THEN 1
                  ELSE 0 END
            )

Using SQL Server CASE statement in WHERE

SQL Server does not have a Bool datatype, so you can't assign or return the result of a comparison as a Bool as you would in other languages. A comparison can only be used with IF-statements or WHERE-clauses, or in the WHEN-part of a CASE...WHEN but not anywhere else.

Your specific example would become this:

SELECT * FROM Customers
WHERE 1=1 OR Country IN ('Germany', 'France', 'UK')

SQL use CASE statement in WHERE IN clause

No you can't use case and in like this. But you can do

SELECT * FROM Product P    
WHERE @Status='published' and P.Status IN (1,3)
or @Status='standby' and P.Status IN  (2,5,9,6)
or @Status='deleted' and P.Status IN (4,5,8,10)
or P.Status IN (1,3)

BTW you can reduce that to

SELECT * FROM Product P    
WHERE @Status='standby' and P.Status IN (2,5,9,6)
or @Status='deleted' and P.Status IN (4,5,8,10)
or P.Status IN (1,3)

since or P.Status IN (1,3) gives you also all records of @Status='published' and P.Status IN (1,3)

Sql Server WHERE IN with CASE

You cannot do that - you need to split it to several checks:

WHERE (ProductLine = 'TVs' AND @ProductType = 1)
   OR (ProductLine IN ('Handsets', 'Mobiles') AND @ProductType = 2)
   OR (ProductLine = 'Books' AND @ProductType NOT IN (1, 2))

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

SQL Server Case statement in WHERE Clause

As a case statement, you would write this as:

CASE WHEN f.PartName = 'B' and e.RecoverableFlag = 1 then 1
     WHEN f.ParName = 'A' then 1
     ELSE 0   END   ) = 1

Is this the logic you want?

Many would think that the case statement is irrelevant here, and instead use:

WHERE ((f.PartName = 'B' and e.RecoverableFlag = 1) or (f.partName <> 'B')) . . .

Conditional 'Case When' within 'Where' Clause T-SQL

You can't use a case statement like that (evaluating different boolean expressions based on input), but you can rewrite your logic with boolean AND and OR instead:

where 
    ([days same form] <= 9 and 
     datediff(month, leasedate ,'2016-08-01 00:00:00') >= 8)
    or 
    ([days same form] > 9 and 
     datediff(month, rentlastchanged ,'2016-08-01 00:00:00') >= 12))

Checking existence of a record in a SQL table using case statement

Your query will only ever return a row if it exists, so the case statement is redundant, you could just as well write

SELECT 'Found' FROM services s WHERE s.idaccount IN (1421)

Although it makes very little sense, you could write something like:

SELECT CASE
         WHEN EXISTS (SELECT 1 FROM services WHERE idaccount = 1421)
         THEN 'Found'

         ELSE 'NotFound'
       END

Note lack of FROM clause in the outermost SELECT. Quicker way to write the same thing:

SELECT COALESCE((SELECT 'Found' FROM services WHERE idaccount = 1421), 'NotFound')

---

Related Topics