Intra-SELECT variables?

Wou开发者_运维技巧ld it be possible to alias an expression returned by a SELECT statement in order to refer to it in other parts of this same SELECT as if it would be a column among others ?

A kind of "temporary variable" whose scope would be limited to the SELECT statement, a little bit like the WITH clause before a SELECT to use a temporary named recorset.

A naive sample of what I'd like to achieve :

SELECT
FIRSTNAME + ' ' + NAME AS FULLNAME,
CASE WHEN LEN(FULLNAME)>3 THEN 1 ELSE 0 END AS ISCORRECT
FROM USERS

where FULLNAME could be used to determine the subsequent output field ISCORRECT, though not being a real column of the table USERS... instead of this laboured error-prone (but working) copy/paste :

SELECT
FIRSTNAME + ' ' + NAME AS FULLNAME,
CASE WHEN LEN(FIRSTNAME + ' ' + NAME)>3 THEN 1 ELSE 0 END AS ISCORRECT
FROM USERS

This sample well describes what I want, but I can easily imagine similar needs where FULLNAME might also be used in other parts of the SELECT statement : in a JOIN, in the WHERE, in a GROUP BY, ORDER BY, etc.

PS : I use SQL Server 2005 but would be also interested in any 2008-specific answer.

Thanks a lot ! :-)

Edit :

In spite of my high respect towards those of you proposing to use a side- or inner-query, I don't feel at ease with such possibilities. My sample really is a naive one. The true queries are rather with 30 output fields including complex expressions (including calls to CLR functions), 15 inner/left outer joins, and 20 additionnal where criteria. I suspect I had rather not multiplying to many indirections towards co-queries if I can avoid it.

I believe you would have to put it in an inner query, and then be able to refer to it outside of the query.

Simplest example based on yours:

select a.fullname, case when len(a.fullname) > 3 then 1 
                        else 0 end as incorrect
from (select firstname + ' ' + name as fullname
      from users) a

Example with a CTE

;with names (FULLNAME) as (
    SELECT FIRSTNAME + ' ' + NAME
    FROM USERS
) select
    FULLNAME,
    CASE WHEN LEN(FULLNAME) > 3 THEN 1 ELSE 0 END AS ISCORRECT
FROM names

You can use cross apply to concatenate strings or do calculations etc.. that involves just the current row.

select T.fullname,
       case when len(T.fullname) > 3 
          then 1
          else 0
       end iscorrect
from users as U
  cross apply
    (select U.firstname+' '+U.name) as T(fullname)
order by T.fullname

Though not very satisfied with it, I choose (temporarily ?) a third option : I avoid co-queries and copy/pasting my complex hard-to-read expression (here symbolized by the simple one aliased as FULLNAME) by embeddind it in a scalar function... which is therefore called several times in different parts of my SELECT.

SELECT
dbo.GetFULLNAME(FIRSTNAME,NAME) AS FULLNAME,
CASE WHEN LEN(dbo.GetFULLNAME(FIRSTNAME,NAME))>3 THEN 1 ELSE 0 END AS ISCORRECT
FROM USERS

What do you think of it ? (I precise that though more complex and unreadable than in my OP, the real expression remains a "simple" matter of string manipulation using several input fields, and doesn't involve any sub-querying or anything like that).