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

without a case statement...

SELECT column1, column2
FROM viewWhatever
WHERE
    (@locationType = 'location' AND account_location = @locationID)
    OR
    (@locationType = 'area' AND xxx_location_area = @locationID)
    OR
    (@locationType = 'division' AND xxx_location_division = @locationID)

Here you go.

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

I'd say this is an indicator of a flawed table structure. Perhaps the different location types should be separated in different tables, enabling you to do much richer querying and also avoid having superfluous columns around.

If you're unable to change the structure, something like the below might work:

SELECT
    *
FROM
    Test
WHERE
    Account_Location = (
        CASE LocationType
          WHEN 'location' THEN @locationID
          ELSE Account_Location
        END
    )
    AND
    Account_Location_Area = (
        CASE LocationType
          WHEN 'area' THEN @locationID
          ELSE Account_Location_Area
        END
    )

And so forth... We can't change the structure of the query on the fly, but we can override it by making the predicates equal themselves out.

EDIT: The above suggestions are of course much better, just ignore mine.

The problem with this is that when the SQL engine goes to evaluate the expression, it checks the FROM portion to pull the proper tables, and then the WHERE portion to provide some base criteria, so it cannot properly evaluate a dynamic condition on which column to check against.

You can use a WHERE clause when you're checking the WHERE criteria in the predicate, such as

WHERE account_location = CASE @locationType
                              WHEN 'business' THEN 45
                              WHEN 'area' THEN 52
                         END

so in your particular case, you're going to need put the query into a stored procedure or create three separate queries.

CREATE PROCEDURE [dbo].[Temp_Proc_Select_City]
    @StateId INT
AS  
        BEGIN       
            SELECT * FROM tbl_City 
                WHERE 
                @StateID = CASE WHEN ISNULL(@StateId,0) = 0 THEN 0 ELSE StateId END ORDER BY CityName
        END

Try this query, it's very easy and useful: Its ready to execute!

USE tempdb
GO

IF NOT OBJECT_ID('Tempdb..Contacts') IS NULL
    DROP TABLE Contacts

CREATE TABLE Contacts(ID INT, FirstName VARCHAR(100), LastName VARCHAR(100))
INSERT INTO Contacts (ID, FirstName, LastName)
SELECT 1, 'Omid', 'Karami'
UNION ALL
SELECT 2, 'Alen', 'Fars'
UNION ALL
SELECT 3, 'Sharon', 'b'
UNION ALL
SELECT 4, 'Poja', 'Kar'
UNION ALL
SELECT 5, 'Ryan', 'Lasr'
GO
 
DECLARE @FirstName VARCHAR(100)
SET @FirstName = 'Omid'
 
DECLARE @LastName VARCHAR(100)
SET @LastName = '' 
 
SELECT FirstName, LastName
FROM Contacts
WHERE  
    FirstName = CASE
    WHEN LEN(@FirstName) > 0 THEN  @FirstName 
    ELSE FirstName 
    END
AND
    LastName = CASE
    WHEN LEN(@LastName) > 0 THEN  @LastName 
    ELSE LastName
    END
GO

This worked for me.

The 4 digit century is determined by the rule, if the year is 50 or more, the century is 1900, otherwise 2000.

Given two 6 digit periods that mark the start and end period, like a quarter, return the rows that fall in that range.