Using the field alias name in WHERE clause

Hi,

Is there anyway to use the field alias name within the WHERE part of a
SQL statement in MS SQL Server 2005? Below is an example:

Select
Table1.Name as FullName,
Table1.Address as Address
from
Table1
Where
FullName like '%test%'

What I'm doing is writing a Stored Procedure that accepts a number of
parameters, then dynamically creates a SQL statement to pull in data
for a look-up table. Given that the field names may change we're
using field aliasing. I'm adding two parameters for filter field and
filter value, but with me not wanting to hardcode field names within
the application, I'd like to use the alias here as well... but it
would be dynamically entered into the WHERE part of the SQL statement.

I know some folks will be asking 'why do you want to do this', so I
figured I'd throw out a quick summary of my purpose. But bottom line
is i'd like to find someway to use the alias name within the WHERE
clause.

Thanks --

Alex

On May 15, 11:33 am, "Aaron Bertrand [SQL Server MVP]"
wrote:
> The, the SELECT list is parsed second-last (before ORDER BY) so the rest of
> the query knows nothing about it.  Use a CTE, e.g.
>
> WITH t AS
> (
>     SELECT FullName = Table1.Name,
>         Address = Table1.Address
>     FROM Table1
> )
> SELECT FullName, Address
> FROM t
> WHERE FullName LIKE '%test%';
>
> "Alex" wrote in message
>
>
>
>
>
> > Hi,
>
> > Is there anyway to use the field alias name within the WHERE part of a
> > SQL statement in MS SQL Server 2005?  Below is an example:
>
> > Select
> >   Table1.Name as FullName,
> >   Table1.Address as Address
> > from
> >   Table1
> > Where
> >   FullName like '%test%'
>
> > What I'm doing is writing a Stored Procedure that accepts a number of
> > parameters, then dynamically creates a SQL statement to pull in data
> > for a look-up table.  Given that the field names may change we're
> > using field aliasing.  I'm adding two parameters for filter field and
> > filter value, but with me not wanting to hardcode field names within
> > the application, I'd like to use the alias here as well... but it
> > would be dynamically entered into the WHERE part of the SQL statement.
>
> > I know some folks will be asking 'why do you want to do this', so I
> > figured I'd throw out a quick summary of my purpose.  But bottom line
> > is i'd like to find someway to use the alias name within the WHERE
> > clause.
>
> > Thanks --
>
> > Alex- Hide quoted text -
>
> - Show quoted text -

That is exactly what I was looking for ... thanks !

Alex