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 MyraI 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
精彩评论