WHERE with an Alias doesn't work

I'm combining first name, last name, middle name, and an ID number together into an alias. Then I need to match that alias with a variable passed to the page (its a search results page). The problem is it claims that there is no table with the name of my alias. Anyone know what I'm doing wrong?

A mockup of the SQL looks like this:

SELECT UserID, Last_Name + ', ' + First_Name + ' ' + Middle_Name + '.' AS name
FROM Table
WHERE name LIKE 'variable%'


Everything looks right with the results, if I take out the WHERE clause it has name displayed properly and joined together with the rest of the data in the results properly.

Thanks in advance for any help that can be provided!

from Books OnLine: "column_alias can be used in an ORDER BY clause. However, it cannot be used in a WHERE, GROUP BY, or HAVING clause."

you'll just have to repeat the expression in the WHERE clause

Depending on your application, you might find it better to pass a separate variable for each part of the name. This doesn't answer your question, I know.. but it might be something to consider.

WHERE Table.Last_Name LIKE ?Last
AND Table.First_Name LIKE ?First
AND Table.MiddleName LIKE ?Middle