SQL Server - Select AS temp_name FROM table WHERE temp_name = something?

Question

You know how you can assign a temporary column name to a return value in a SQL statement like this?

SELECT something+this+that AS myvalue FROM mytable

Is it possible to use the temporary name myvalue as a conditional?

SELECT something+this+that AS myvalue FROM mytable WHERE myvalue = 10

I can't seem to figure out the syntax to do this. The only way I can figure out how to do it is to rewrite out the entire column definition again like this:

SELECT something+this+that AS myvalue FROM mytable WHERE something+this+that = 10

Obviously the above example is a trivial example. My query I need to do this on is extremely complexed, so having the rewrite each returned column definition for each conditional will be quite a hassle.

So anyways, what is the syntax for this? Is it possible?

Answer 1

I don't think it is possible, but you may cheat by putting the data into a temporary table, and then running a second query to run the rest.

That, or dynamic SQL, but I'm not a fan of the latter.

Answer 2

Use a derived table...

SELECT
    myvalue
FROM
    (
    SELECT something+this+that AS myvalue FROM mytable
    ) foo
WHERE
    myvalue = 10

Or use a CTE which looks more elegant but is the same

;WITh myCTE AS
(
    SELECT something+this+that AS myvalue FROM mytable
)
SELECT
    myvalue
FROM
    myCTE
WHERE
    myvalue = 10

Or a computed column so it's usable normally

ALTER TABLE MyTable ADD myvalue AS something+this+that