Q:

SQL - using SELECT Alias in WHERE

I am having a heck of a time getting this query to work. Basically I need to do a CASE statement to get the person's NickName if they have one, otherwise use their FirstName. Then in the WHERE statement, do a LIKE statement on that above CASE statement.
I've been able to get it to work if I only do the FirstName/NickName but when I add in other columns it stops working.
Here is what I've got
SELECT
    LastName
  , Company
  , Status
  , CASE
      WHEN NickName = '' THEN FirstName
      WHEN NickName IS NULL THEN FirstName
      ELSE NickName
    END AS FName
FROM database
WHERE
       Status = 'Active'
  AND  Company = '@Company'
  AND  FName + ' ' + LastName LIKE '%@search%'
  OR   LastName + ', ' + FName LIKE '%@search%'

Obviously the above doesn't work because I'm trying to use an alias in my WHERE clause. I get
Msg 207, Level 16, State 1, Line 14
Invalid column name 'FName'.
Msg 207, Level 16, State 1, Line 15
Invalid column name 'FName'.

Any help is greatly appreciated. Thanks!

A:

You can use a Common Table Expression or Subquery to create the FName as a kind of virtual column of a new resultset.  You can that use the resultset as a kind of virtual table and filter on that in the where clause.
SELECT *
FROM (
  SELECT
    LastName
    , Company
    , Status
    , CASE
        WHEN NickName IS NULL OR NickName = '' THEN FirstName
        ELSE NickName
      END AS FName
  FROM database
  WHERE Status = 'Active'
  AND  Company = '@Company'
) FNames
WHERE FName + ' ' + LastName LIKE '%@search%'
   OR LastName + ', ' + FName LIKE '%@search%'

