Dynamic column in where clause

I'm trying to execute a query like this:

SELECT Id,Name,Distance=dbo.CalculateDistance(Lat,Lon,@lat,@lon)
FROM Requests
WHERE Distance < 2
ORDER BY Distance DESC

Error says,there is no Dis开发者_如何转开发tance column. I tried this once but it cuts off query performance

SELECT Id,Name,Distance=dbo.CalculateDistance(Lat,Lon,@lat,@lon)
FROM Requests
WHERE dbo.CalculateDistance(Lat,Lon,@lat,@lon) < 2
--ORDER BY Distance DESC

Without doing a second calculation,how can I achieve above query ?

Best Regards

Myra

I found the link below suggesting that you make a nested select and filter on its values; in this case the scalar computation will only be performed once.

So you should be able to do something like

        SELECT Id, Name, Distance FROM (
        SELECT Id,Name,Distance=dbo.CalculateDistance(Lat,Lon,@lat,@lon)
        FROM Requests) derived 
        WHERE Distance < 2 
        ORDER BY Distance DESC 

I had a brief look at the execution plan, and the scalar computation only happens once. But you should certainly check the use of indices etc.

I hope it is om some help though.

Avoid Transact-SQL's Column Alias Limitations