Can a WHERE clause refer to a column based on a subquery ?

I've got a stored procedure that contains a query similar to the following which works.
(The real query contains lots more columns and the subquery is quite long)

SELECT table1.ColA, table1.ColB, (SELECT TOP 1 colX from table2 WHERE etc. ) FROM table1

Sometimes the sub-query returns nulls which is fine.

I now want to modify the stored procedure so that it will optionally only return the rows where the sub-query returns non-null values. I've added an int parameter @.filter to the procedure. If @.filter = 0 I will return all rows; if @.filter = 1 I will filter the results.

The query now looks like this:

SELECT table1.ColA, table1.ColB, subquery = (SELECT TOP 1 colX from table2 WHERE etc. ) FROM table1
WHERE (@.filter = 0 or not subquery is null).

However, I get an invalid column name 'subquery' error.

Have I simply got my syntax wrong or is it not possible to do this. I've played with a few variations such as '(SELECT ....) as subquery' but nothing seems to work.

I can imagine a solution where the sub-query is used to generate a table that table1 can be inner joined with, but the query is quite big (and has been tested) and I didn't want to start hacking it about if there was a trivial fix.

Any help appreciated.I have found the answer to my own question.

The SELECT parameters (including the sub-query) are not evaluated until after the WHERE so what I wanted to do is not possible.

I found the link below useful.

http://databases.aspfaq.com/database/how-do-i-use-a-select-list-alias-in-the-where-or-group-by-clause.html|||i din get ur exact requirement , but yes the syntax is not correct....
read a bit abt 'corelated subqueries' from books online..u'll hopefuly get a solution...
if u dont post back , i'll try to put some example...|||

The SELECT list expressions can be evaluated before the WHERE clause in SQL Server. But that has nothing to do with the problem though. Column aliases are only part of the result set metadata that the client uses. As such it can be used only in the ORDER BY clause (in ANSI SQL, the ORDER BY belongs on the client-side & hence it is allowed in ORDER BY). You can use derived table to simplify complex SELECT statements and avoid repeating expressions like:

SELECT ColA, ColB, ColN

FROM (

SELECT table1.ColA, table1.ColB

, (SELECT TOP 1 colX from table2 WHERE etc. ) as ColN

FROM table1

) as t

WHERE (@.filter = 0 or ColN is not null)

|||Thanks for that.

It works now.