Help Needed On User Defined Function

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

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

Help Needed On User Defined Function

Author

Topic

SilentCodingOne
Starting Member

20 Posts

Posted - 2008-12-13 : 01:59:52

I'm working on my homework and cannot seem to get one of the problems to work properly. I have to create a user defined function that accepts latitude and longitude values for two locations and returns the distance between them. The test values are latitude 3.0664 and longitude 118.3103 for point s and latitude 33.6784 and longitude 118.0054 for point p. If my math is right the distance should come to 1028.280141363536 but when I try to run the function I get 0. The following is the code for the function:


CREATE FUNCTION udf_Distance(@distance decimal, @sLat decimal, @pLat decimal,
@sLon decimal, @pLon decimal)
RETURNS decimal
AS
BEGIN
SET @distance = 
	SQRT(((69.17 * (@sLat - @pLat)) * (69.17 * (@sLat - @pLat)) 
	+ (57.56 * (@sLon - @pLon)) * (57.56 * (@sLon - @pLon))))
RETURN @distance
END

Here is the statement that calls the function:


SELECT dbo.udf_Distance (34.0664, 33.6784, 118.3103, 118.0054) AS Distance

Thanks in advance for the help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-13 : 02:03:41

why have you not defined precision and scale for decimal variables?


CREATE FUNCTION udf_Distance(@distance decimal(p,s), @sLat decimal(p,s), @pLat decimal(p,s),
@sLon decimal(p,s), @pLon decimal(p,s))
RETURNS decimal
AS
BEGIN
SET @distance = 
	SQRT(((69.17 * (@sLat - @pLat)) * (69.17 * (@sLat - @pLat)) 
	+ (57.56 * (@sLon - @pLon)) * (57.56 * (@sLon - @pLon))))
RETURN @distance
END