When using a static column with SELECT, why can't I access that column from WHERE clause?

I have never used MySQL; however, SQL Server will not let you access columns created entirely in the select clause in your where clause, and would give a "Invalid Column name" error if you try. This is because it is looking explicit at the columns available from entities in the FROM clause when it matches to the where clause.

Using a subquery will not always make SQL Server evaluate a static formula multiple times though, depending on how you write the query and exactly what the engine has to do. If you look at the execution plan for something like:

select *
from
(select *, 1 as b
from dbo.Test ) as bt
where bt.b = 1

It will show that the compute scalar part of the plan has an I/O cost of 0 and an estimated number of executions of 0, regardless of how large the test table is.

Incidentally, it seems that there should be little reason to use a static formula (one which does not actually reference any columns from the base tables) in the where clause and a formula that does reference a column really would need to be evaluated seperately for each row.

Thank you.

While exploring this problem further, I wrapped my head around the syntax more and see why access to the select clause is not available from the where clause. This is so that functions called from the select clause to generate a row are not called if a row is not generated.

The the main reason that I am using a static formula is so that my prepared statement doesn't need to have reference to the same variable several times. A simple example would be:

SELECT *
FROM a INNER JOIN b ON a.id=b.id
WHERE a.i < ? AND b.i < ?;  # the ? are representing the same number

SELECT *
FROM (
    SELECT *, ? AS constant # only one ? reference used
    FROM a INNER JOIN b ON a.id=b.id
) AS foo
WHERE a.i < a.constant AND b.i < constant;

Cheers