Calculated value in both SELECT and WHERE clauses

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculated value in both SELECT and WHERE clauses

Author

Topic

msch-prv
Starting Member

11 Posts

Posted - 2011-02-15 : 14:58:53

Hi, I have searched quite a bit to no avail. I'm wondering if there exists a clever trick to avoid duplicating processing when a calculated value is used in both the SELECT and WHERE clauses.

The below query returns all businesses within a prescribed radius. How could I ensure that the sproc is only run once so that the calculated value could be included in the SELECT part as well? TIA.

SELECT 
	BusID, BusName, BusAddr1, BusCity, BusZip, BusTel1, BusLat, BusLng
	--, dbo.vid_fn_GetEarthDistance(BusLat, BusLng, @lat, @lng) AS Dist
FROM	
	vid_Bus
WHERE	
	dbo.vid_fn_GetEarthDistance( BusLat, BusLng, @lat , @lng) <= @radius
		AND BusDel = 0  

ORDER BY 
	BusName, BusCity

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-15 : 15:09:25

could not understand the following "How could I ensure that the sproc is only run once so that the calculated value could be included in the SELECT part as well?" can you shed some more light on this ?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-15 : 15:18:30

dbo.vid_fn_GetEarthDistance( BusLat, BusLng, @lat , @lng) is only being calculated once for each record-- actually for every record record in the table.

Jim

Everyday I learn something that somebody else already knew

msch-prv
Starting Member

11 Posts

Posted - 2011-02-15 : 15:27:26

Thanks for the quick feed-back. Actually, I found an answer. The following seems to work:

SELECT 
	tmp.BusID, tmp.BusName, tmp.BusTypeIdx, tmp.BusAddr1, tmp.BusCity, tmp.BusZip, tmp.BusTel1, tmp.BusLat, tmp.BusLng, tmp.Dist
	FROM (
	   SELECT  BusID, BusName, BusTypeIdx, BusAddr1, BusCity, BusZip, BusTel1, BusLat, BusLng, BusDel, dbo.vid_fn_GetEarthDistance(BusLat, BusLng, @lat, @lng) AS Dist
           FROM  vid_Bus
	) tmp

WHERE	
	tmp.Dist <= @radius AND
		 tmp.BusDel = 0  -- 0 : FALSE, -1: TRUE 

ORDER BY 
	tmp.BusName, tmp.BusCity

msch-prv
Starting Member

11 Posts

Posted - 2011-02-15 : 15:30:55

Ad jimf.

Thanks for your comment.
Does that mean that there would be no processing penalty by including the sproc in the SELECT and WHERE clauses then? (I don't have the tools to measure execution speed.)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources